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.
I have the following Matrix:
Month january february
Description Value Value TOTAL
Administration 100 600 700
Taxes and fees 60 100 160
Staff 40 300 340
TOTAL 200 1000 1200
If I click on any description a drill down happens, for example, if I click on "Administration" I must see it:
Month january february
Description Value Value TOTAL
Administration 100 600 700
Water 25 300 325
Cellphone 50 250 300
Light 25 50 75
Taxes and fees 60 100 160
Staff 40 300 340
TOTAL 200 1000 1200
My data has two tables:
| Bill | | Date |
| category | | date |
| subcategory |
| value |
Until now, it's all right. But the problem is that I have to include a new column/measure to my matrix, containing the percentage of each description. It has to be like this:
Month january february TOTAL
Description Value Percentage Value Percentage VALEU PERCENTAGE
Administration 100 50% 600 60% 700 58.3%
Taxes and fees 60 30% 100 10% 160 13.3%
Staff 40 20% 300 30% 340 28.3%
TOTAL 200 100% 1000 100% 1200 100.0%
Click on "Administration":
Month january february TOTAL
Description Value Percentage Value Percentage VALEU PERCENTAGE
Administration 100 50.0% 600 60% 700 58.30%
Water 25 12.5% 300 30% 325 27.00%
Cellphone 50 25.0% 250 25% 300 25.00%
Light 25 12.5% 50 5% 75 06.30%
Taxes and fees 60 30.0% 100 10% 160 13.3%
Staff 40 20.0% 300 30% 340 28.3%
TOTAL 200 100% 1000 100% 1200 100.0%
How can I do that?
Thanks.
Solved! Go to Solution.
@Anonymous
I suspect you are using the Matrix Preview which filters the data!!!
You do need % of Column Total but use the Regular Matrix!
OR
If you do go with the Matrix Preview - use it only - At the lowest level of Data!!!
Here's you % of CT MEASURE which should take care of this vs the Quick Calc % of CT
% of CT MEASURE = DIVIDE ( SUM ( Table1[Value] ), CALCULATE ( SUM ( Table1[Value] ), ALL ( Table1[Subcategory], Table1[Category] ) ), 0 )
Hope this helps!
EDIT: Built in % of CT Quick Calc vs the % of CT Measure
Hi @Anonymous,
You can refer to below steps to achieve your requirement.
Steps:
1. Create matrix visual with your needed columns and turn on the row total. (Date to Rows field, value to Value field, month to Columns field)
2. Add a value column to value field, right click and turn on the "Qiuck Calc" panel.
3. Select "Percent of column total" at "show value as" option.
Result:
In addition, if you want drill down the hierarchy value, you can try to use the newest preview feature "New matrix visual", it support to drill down the data.
Regards,
Xiaoxin Sheng
Hi, @v-shex-msft, thanks for helping me. Your solution using Quick Calc works, but this solution does not fit my needs. Using Quick Calc on my drill down I have the percentage of my column. But my needs, only for a drill down, is a percentage of the drill up column. For example:
Without drill down hierarchy, both, My Needs and Quick Calc works:
Month january february TOTAL
Description Value Percentage Value Percentage VALEU PERCENTAGE
Administration 100 50% 600 60% 700 58.3%
Taxes and fees 60 30% 100 10% 160 13.3%
Staff 40 20% 300 30% 340 28.3%
TOTAL 200 100% 1000 100% 1200 100.0%
But, when I make a drill down the problem happens:
Quick Calc
Month january february TOTAL
Description Value Percentage Value Percentage VALEU PERCENTAGE
Administration 100 50.0% 600 60% 700 58.30%
Water 25 25.0% 300 30% 325 27.00%
Cellphone 50 50.0% 250 25% 300 25.00%
Light 25 25.0% 50 5% 75 06.30%
Taxes and fees 60 30.0% 100 10% 160 13.3%
Staff 40 20.0% 300 30% 340 28.3%
TOTAL 200 100% 1000 100% 1200 100.0%
My Needs
Month january february TOTAL
Description Value Percentage Value Percentage VALEU PERCENTAGE
Administration 100 50.0% 600 60% 700 58.30%
Water 25 12.5% 300 30% 325 27.00%
Cellphone 50 25.0% 250 25% 300 25.00%
Light 25 12.5% 50 5% 75 06.30%
Taxes and fees 60 30.0% 100 10% 160 13.3%
Staff 40 20.0% 300 30% 340 28.3%
TOTAL 200 100% 1000 100% 1200 100.0%
Do you understand what are my needs? Can you help me?
Thanks.
@Anonymous
I suspect you are using the Matrix Preview which filters the data!!!
You do need % of Column Total but use the Regular Matrix!
OR
If you do go with the Matrix Preview - use it only - At the lowest level of Data!!!
Here's you % of CT MEASURE which should take care of this vs the Quick Calc % of CT
% of CT MEASURE = DIVIDE ( SUM ( Table1[Value] ), CALCULATE ( SUM ( Table1[Value] ), ALL ( Table1[Subcategory], Table1[Category] ) ), 0 )
Hope this helps!
EDIT: Built in % of CT Quick Calc vs the % of CT Measure
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |