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 facing an issue with the cumulative percentage totals. The table being used is Matrix preview.
I have 3 levels in my report like a hierarchy. The measure fields used are Budget,Actual,Difference and Difference %.
The Difference % column gives the correct results only at the lowest level like L111,L112 and so on.
At the 1st and 2nd levels the difference % just adds up the percentages at the lowets levels which is incorrect.
The difference % column when i do a summarize or no summarize the result is the same.
Kindly let me know if i can use any DAX functionality in this case.
Solved! Go to Solution.
Hi @sudhakar111,
But i need to show it as 2016-Apr,2016-May and so on in that order.
It is only showing in alphabetical order like 2016-Apr,2016-Aug and so on.
You should be able to follow steps below to get this work.
1. Select "Year Month" column.
2. Click Sort by Column under Modeling tab.
3. Select "Month Year"
Regards
Hi @sudhakar111,
After looking into your shared pbix file, I find that you're using a calculate column to calculate the percentage. So the percentage will be added up at the 1st and 2nd levels.
In this scenario, you should create a measure instead to calculate the percentage, then it should work as expected in the Matrix preview.
Difference % Measure = DIVIDE ( SUM ( Merge1[Budget] ) - SUM ( Merge1[Actual] ), SUM ( Merge1[Budget] ) )
Regards
Thanks a lot v-ljerr-msft, it worked perfectly.
i posted one other issue on the same sample data.
The cross tab has year and month.If it is all numeric it works fine.
But i need to show it as 2016-Apr,2016-May and so on in that order.
It is only showing in alphabetical order like 2016-Apr,2016-Aug and so on.
Hi @sudhakar111,
But i need to show it as 2016-Apr,2016-May and so on in that order.
It is only showing in alphabetical order like 2016-Apr,2016-Aug and so on.
You should be able to follow steps below to get this work.
1. Select "Year Month" column.
2. Click Sort by Column under Modeling tab.
3. Select "Month Year"
Regards
Thank you so much v-ljerr-msft, it works perfectly. The mistake i made was trying to sort on the same column.
what is your measure calculation?
Proud to be a Super User!
It is Difference % = Budget-Actual/Budget
Hi @sudhakar111,
Could you post your table structures(including the measures) with some sample data which can reproduce the issue, so that we can better assist on it? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Hi v-ljerr-msft,
Uploaded the PBIX file to one drive. It has only sample data. Kindly check it and let me know.
https://1drv.ms/u/s!Antetl7mO6quaa5Tb7gN1mjC6CM
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |