{"id":2327,"date":"2020-06-06T18:10:24","date_gmt":"2020-06-06T18:10:24","guid":{"rendered":"https:\/\/portfolio.accrualhub.com\/powergi2\/?p=249"},"modified":"2026-05-14T16:34:36","modified_gmt":"2026-05-14T16:34:36","slug":"parameter-table-with-dax-create-a-slicer-to-change-amounts-to-millions-thousands","status":"publish","type":"post","link":"https:\/\/powergi.net\/es\/blog\/parameter-table-with-dax-create-a-slicer-to-change-amounts-to-millions-thousands\/","title":{"rendered":"Create a slicer to change amounts to millions & thousands"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\n

Suppose we have a table with a measure such as Sum of sales amount, and we would like to give the user the ability to change the way the amounts are shown, using a slicer.<\/p>\n\n

\"\"<\/figure>\n\n

The blue slicer in above example allows the user to change the Sum of sales amounts to be displayed as: Millions(M), Thousands(K) or to be shown as they are in the table.<\/p>\n\n

This is a very simple trick that leverages the usage of a function called HASONEVALUE (), this formula returns true or false if the column used as parameter, has only one distinct value in the current filter context.\u00a0<\/p>

\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t

Turn your ideas into digital solutions<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t

Our team guides you step by step to build custom apps in Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n

This approach is commonly used in Power Platform consulting<\/strong><\/a> to enhance data interaction and user experience in reports.<\/p>\n

As a leader in Power Platform consulting services<\/strong><\/a>, we are committed to transforming the way businesses harness their data. Our expert consultants collaborate closely with you to tailor solutions that maximize efficiency and drive results. Whether it\u2019s implementing advanced data slicers or automating complex tasks, we ensure a seamless integration with your existing systems, empowering you to make data-driven decisions with ease. Explore how we can elevate your data strategy today.<\/p>\n\n

How to do it?<\/h2>\n\n

1.Based on our example above, create a table for the parameter you want to add.<\/p>\n\n

\"\"\n
My data is already in Millions, hence the 1 for the conversion in the value column<\/figcaption>\n<\/figure>\n\n

2. Add this table to the data model.<\/p>\n\n

\"\"<\/figure>\n\n

3. Create a slicer in your pivot table, using the table we just created.<\/p>\n\n

\"\"<\/figure>\n\n

For now the slicer won\u2019t do anything when an option is selected\u2026<\/p>\n\n

\"\"<\/figure>\n\n

4.Create a sum of sales measure or modify the existing measure to look like this:<\/p>\n\n

NASales :=\nIF (\n    HASONEVALUE ( Table1[Selection] ),\n    SUM ( vgsales[NA_Sales] ) * VALUES ( Table1[Value] ),\n    SUM ( vgsales[NA_Sales] )\n)<\/code><\/pre>\n\n
The way to read the formula is the following:<\/p>
\t\t
\n\t\t\t
\n\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
Is your business ready for automation?<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t
Automate processes with Microsoft Power Platform.<\/p>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t
\n\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\n\t\t\t<\/i>\t\t\t<\/a>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t<\/div>\n\n
IF (\n    HASONEVALUE ( Table1[Selection] ), \/\/When the users click on one value of the slicer, this \n                                        expression returns true, because only one distinct value is \n                                        selected in the parameter table, \n    SUM ( vgsales[NA_Sales] ) * VALUES ( Table1[Value] ), \/\/We then multiply the sum of sales by the \n                                                            parameter value of our table, the \n                                                            expression VALUES() returns a scalar value \n                                                            when the column in the parameter has only \n                                                            one distinct value. \n    SUM ( vgsales[NA_Sales] ) \/\/ If the user selects multiply values or makes no selection in the \n                                 slicer we just show the sum of sales according to the original data. \n)\n<\/code><\/pre>\n\n
5. Add the new measure to the pivot table and use the slicer to change its value.<\/p>\n\n
\"\"<\/figure>\n\n
You can use this function to add new ways to interact with your pivot tables, like giving the user the ability to select currency conversions, rounding, taxes, goals % and more.<\/p>\n\n
\u00a0<\/p>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"
Suppose we have a table with a measure such as Sum of sales amount, and we would like to give the user the ability to change the way the amounts are shown, using a slicer. The blue slicer in above example allows the user to change the Sum of sales amounts to be displayed as: […]<\/p>\n","protected":false},"author":3,"featured_media":2636,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"glossary_letter":"a","footnotes":""},"categories":[22],"tags":[16,27,28],"class_list":["post-2327","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dax-cat","tag-excel","tag-pivottables","tag-powerpivot"],"glossary_letter":"a","rank_math_title":"DAX Slicer for Millions & Thousands | Power GI","rank_math_description":"Create dynamic slicers in Power BI using DAX to switch between millions and thousands easily. Enhance your reports with this Power GI guide.","rank_math_focus_keyword":null,"_links":{"self":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2327","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/comments?post=2327"}],"version-history":[{"count":1,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2327\/revisions"}],"predecessor-version":[{"id":13467,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/posts\/2327\/revisions\/13467"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media\/2636"}],"wp:attachment":[{"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/media?parent=2327"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/categories?post=2327"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/powergi.net\/es\/wp-json\/wp\/v2\/tags?post=2327"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}