Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am creating a Metrix visual from a data table that shows the date and daily count of children in different type of foster care placement (3 columns/fields: date, count, placement type). In the screenshot of Metrix visuals below, the top one is the count (sum) of children by year/month and placement type. The bottom one is to show the count as percentage of row totals. Since the Power BI built-in "show value as % as row totals" have 2 decimal points and cannot be formatted, I need to create a dax measure to have the % values. I created one based on a posting in this forum which recommends using the formulae:
PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])
However, as shown in the screenshot above, the row percentage values are right only for the Year 2022 total, but not for the monthly vlaues. The monthly %s are not row %, but as % of column total. I would like to have a dynamic % that shows all monthly and yearly values as % of row totals. But the formulae I used cannot do this.
Can you suggest any dax formulae that I can use to achieve the task?
Thanks.
Solved! Go to Solution.
@LijunChen , This will based on grand total
PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])
If you need by year
PERCENT =divide( SUM([TOTAL SALES]) , calculate(SUM([TOTAL SALES]), removefilters(Table[Month]) ) )
You can also use isinscope to get different subtotals
DAX- ISINSCOPE: https://youtu.be/Tu92J_OtUK0
Percent of Total and Percent of Subtotal: https://youtu.be/9JTO1s0KV2U
@LijunChen , This will based on grand total
PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])
If you need by year
PERCENT =divide( SUM([TOTAL SALES]) , calculate(SUM([TOTAL SALES]), removefilters(Table[Month]) ) )
You can also use isinscope to get different subtotals
DAX- ISINSCOPE: https://youtu.be/Tu92J_OtUK0
Percent of Total and Percent of Subtotal: https://youtu.be/9JTO1s0KV2U
Many thanks. I will check this out and see.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |