Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Calculate Index




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


Resident Rockstar
Resident Rockstar

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




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


 Thank youIndex_LI.jpg



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

Resident Rockstar
Resident Rockstar

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

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.


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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee
Microsoft Employee

Hi @JP2,


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


Best Regards,


Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors