Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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??
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!!!
Thank you
I managed to do that, but is it possible to get a value i marked in the image above. A sort of weighted average.
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,
Dale
Hi @JP2,
What's the formula in math? Please post it here and we can convert it into DAX.
Best Regards,
Dale
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
62 | |
52 | |
39 | |
24 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |