Forum Discussion

sudhakar111's avatar
sudhakar111
Helper IV
9 years ago
Solved

Problem with Cumulative percentage

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.

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

     

    1. Select "Year Month" column.

    2. Click Sort by Column under Modeling tab.

    3. Select "Month Year"

     

     

    Regards

8 Replies

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        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. :smileyhappy:

         

        Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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's avatar
      sudhakar111
      Helper IV
      9 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        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. :smileyhappy:

         

        1. Select "Year Month" column.

        2. Click Sort by Column under Modeling tab.

        3. Select "Month Year"

         

         

        Regards