Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sudhakar111
Helper IV
Helper IV

Problem with Cumulative percentage

Hi All,

 

I am facing an issue with the cumulative percentage totals. The table being used is Matrix preview.

 

Hierarchy.png

 

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.

 

1 ACCEPTED 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. Smiley Happy

 

1. Select "Year Month" column.

2. Click Sort by Column under Modeling tab.

3. Select "Month Year"

 

sortc.PNGsortc2.PNG

 

Regards

View solution in original post

8 REPLIES 8
v-ljerr-msft
Employee
Employee

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] )
)

 r2.PNG

 

 

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. Smiley Happy

 

1. Select "Year Month" column.

2. Click Sort by Column under Modeling tab.

3. Select "Month Year"

 

sortc.PNGsortc2.PNG

 

Regards

Thank you so much v-ljerr-msft, it works perfectly. The mistake i made was trying to sort on the same column.

vanessafvg
Super User
Super User

what is your measure calculation?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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. Smiley Happy

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.