Skip to main content
cancel
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

Reply
JP2
Regular Visitor

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 6
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @JP2,

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

Index.PNG

 

 

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

Index1.PNG

 

 

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 youIndex_LI.jpg

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

Index2.PNG

 

 

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

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.
v-jiascu-msft
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,

Dale

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

Announcements
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