Calculate Index

[JP2]

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??

[Thejeswar]

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]

 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]

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!!!

[JP2]

Hi @Thejeswar

It's not what i'm looking for. That's just the average.

In case of the Index (119.40), product A has a greater share than B and C.

I also want this for the Season Index. So the the 116.75 has to be closer to 135 (greater share product A)

Don't know if this is possible.

[v-jiascu-msft]

Hi @JP2,

Can you share the expected result? And the way to calculate the result in theory? Then the community experts can help to convert it into the formula of Power BI.

Best Regards,
Dale

[v-jiascu-msft]

Hi @JP2,

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

Best Regards,

Dale