Problem with Cumulative percentage

[sudhakar111]

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.

[v-ljerr-msft]

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

[sudhakar111]

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 · 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. 

1. Select "Year Month" column.

2. Click Sort by Column under Modeling tab.

3. Select "Month Year"

Regards

[sudhakar111]

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

[vanessafvg]

what is your measure calculation?

[sudhakar111]

It is Difference % = Budget-Actual/Budget

[v-ljerr-msft]

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

[sudhakar111]

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