Forum Discussion

JP2's avatar
JP2
Regular Visitor
7 years ago

Calculate Index

Hi,

 

 

The following scenario:

 

I want to show an index on different seasons. With a calculated collumn I managed to get the indexes. But i want also a total.

 

Standard Index is (total reveneu/total costs)*100. 

In case of spring : ((total reveneu/total costs)*100) + 5

In case of summer : ((total reveneu/total costs)*100) -30

In case of winter : ((total reveneu/total costs)*100) + 10

 

Product   Season        Revenue       Cost       Index         Season Index

A               spring         1000              800        125           130

B               winter         100               105          95             105

C               winter         100                100        100           110

 

Standard index total (1000+100+100)/(800+105+100)*100 = 119. No problem with Power BI

Season Index total??? How to calculate with Power BI??

 

6 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    7 years ago

    Hi JP2,

    I suppose the below values shows in the image is the one that you are looking for

     

     

    If my understanding is correct, you are having two conditional columns

     

    1. Index - (total reveneu/total costs)*100

    2. Seasonal Index - This will have an if condition on top your Index Column

     

    When you use it in your report, your Index and Seasonal Index Values will be proper except the Total for Index Column. This is because since you are having Index as a calculated column, the Aggregation set to the column will apply for the totals as well

     

    Please find below screenshot

     

     

    In this screenshot, the Total for Index is shown as 320.24 because the Aggregation property set to the Index column is Sum and hence is applied for the SubTotals as well

     

    To handle this, do the following

     

    Use Index as a measure

     

    Measure (Index) = SUM(SeasonIndex[Revenue])/ SUM(SeasonIndex[Cost]) * 100

    This will give you the value of 119. But the limitation is this measure cannot be directly used in an IF condition in a Callculated Column. This can be handled in 2 ways.

     

    1. Have a separate Index Column, This can be used inside the IF Statement for Seasonal Index (This is not displayed for the Index column in the report) and the Measure for Index should be displayed in the report

     

    or

     

    2. Create a measure for Seasonal Index as well using the below DAX

     

    Season Index = IF(MAX(SeasonIndex[Season]) = "winter", SeasonIndex[Index1] + 10, IF(MAX(SeasonIndex[Season]) = "Spring", SeasonIndex[Index1] + 5, IF(MAX(SeasonIndex[Season]) = "Summer", SeasonIndex[Index1] - 30)))

    Here I am using MAX () with season just because IF statment requires a Aggregate Column to be used. But here you will get to see the Total Values for the Seasonal Index column which might not be a proper value and as per my reasearch you cannot stop it from being displayed

     

    Hope it is clear!!!

    • JP2's avatar
      JP2
      Regular Visitor
      7 years ago

       

       Thank you

      Thejeswar

       

      I managed to do that, but is it possible to get a value i marked in the image above. A sort of weighted average.

      • Thejeswar's avatar
        Thejeswar
        Super User
        7 years ago

        Hi JP2,

        When you say Weighted Average, I asume that you refer to AVG(Column).

         

        In continuation to my prev. answer, after you create a Season Index calculated column using the below DAX

         

        Season Index = IF(MAX(SeasonIndex[Season]) = "winter", SeasonIndex[Index] + 10, IF(MAX(SeasonIndex[Season]) = "Spring", SeasonIndex[Index] + 5, IF(MAX(SeasonIndex[Season]) = "Summer", SeasonIndex[Index] - 30)))

        Create a new calculated measure, that will hold the average value of this column using the below DAX

         

        Season Index1 = AVERAGE(SeasonIndex[Season Index])

        This will give you weighted Average in place of SubTotal and the Actual value in place of the line items as shown below

         

         

        The Above image shows the values of both Season Index Column and Season Index1 Measure. As you can see, the Season Index Column is just showing the Sum of values with the Season Index1 Measure is showing the Weighted Average of Season Index1

         

        In your actual case, you need not have to display the Season Index Column.

         

        Hope this is what you are looking for!!!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi JP2,

     

    What's the formula in math? Please post it here and we can convert it into DAX.

     

    Best Regards,

    Dale