cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sudhakar111 Regular Visitor
Regular Visitor

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

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Problem with Cumulative percentage

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

8 REPLIES 8
Super User
Super User

Re: Problem with Cumulative percentage

what is your measure calculation?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
sudhakar111 Regular Visitor
Regular Visitor

Re: Problem with Cumulative percentage

It is Difference % = Budget-Actual/Budget

v-ljerr-msft Super Contributor
Super Contributor

Re: Problem with Cumulative percentage

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

sudhakar111 Regular Visitor
Regular Visitor

Re: Problem with Cumulative percentage

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

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Problem with Cumulative percentage

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

Highlighted
sudhakar111 Regular Visitor
Regular Visitor

Re: Problem with Cumulative percentage

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.

v-ljerr-msft Super Contributor
Super Contributor

Re: Problem with Cumulative percentage

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

sudhakar111 Regular Visitor
Regular Visitor

Re: Problem with Cumulative percentage

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