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 have some trouble in getting the percentage value of the column total. Below is my data
The top table shows the number of orders for a specific fruit in a spefic country. The measure created is distinctcount(orders)
What I want to do here, is to get the table below, which shows the percentage by column.
1st method: I can do this by using shown value as column total in the PBI, however, the percentage shows has two decimals, which I want 0 decimals. If there is a way to change the decimals, I can use this method. Does anyone know how to do this?
2. By creating a measure myself
Solved! Go to Solution.
Hi @abbynie08
According to summary by Greg_Deckler :
This is almost certainly caused by what is referred to as “the measure totals problem”. This one is extremely common. See this post that explains it, Dealing with Measure Totals
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.
Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.
Or this Quick Measure submission, Table Matrix Totals or Subtotals.
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
Best Regards
Maggie
Hi @abbynie08
According to summary by Greg_Deckler :
This is almost certainly caused by what is referred to as “the measure totals problem”. This one is extremely common. See this post that explains it, Dealing with Measure Totals
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.
Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.
Or this Quick Measure submission, Table Matrix Totals or Subtotals.
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
Best Regards
Maggie
Hi @abbynie08
Create a measure
Measure = DISTINCTCOUNT('Table'[id])/CALCULATE(DISTINCTCOUNT('Table'[id]),ALL('Table'[cate]))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Does this measure work?
Thanks for replying. When I put in your code, it shows an eror, "Multiple table arguments are not allowed in the ALL/ALLNOBLANKROW function." I think what we can do is to put only the column name in all function.
However, there is a question. When I put in "Perent of Total = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Fruit])))". It shows 100%. But, the intersting thing is when I put in "Perent of Total Row = DIVIDE(DISTINCTCOUNT('Table'[Orders]),CALCULATE(DISTINCTCOUNT('Table'[Orders]),ALL('Table','Table'[Country]))). It shows the percentage of each row total.
So the tricky part is why does this funciton works on the row, not on total?
Thanks
Hi,
Share the link from where i can download your PBI file and show the expected result there very clearly.
Try your measure with a KEEPFILTERS added.
Perent of Total =
DIVIDE (
DISTINCTCOUNT ( 'Table'[Orders] ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[Orders] ),
KEEPFILTERS ( ALLEXCEPT ( 'Table', [Country] ) )
)
)
Thank you for replying. Unfortunately, this does not work. The percentages in the matrix are all 100%. I tried to create a measure of half of your code, which list below to see why that happens.
Column Total =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Orders] ),
KEEPFILTERS ( ALLEXCEPT ( 'Table', [Country] ) )
)
It seems that the value returned are just as the first table that I put in my post, which is the same as distinctcount('Table' [Orders]). Can you help me with this? Thanks
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |