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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Calculate index base 100 for different products

Hi,

I need to calculate index feature for different products for my report.

 

For example, user can select date 2020.01.01. 

Output would be 

Product A - Value at 2020.01.01 = 100.  Value at other dates = Value at that date/Value at 2020.01.01 * 100

Product B - Value at 2020.01.01 = 100.  Value at other dates = Value at that date/Value at 2020.01.01 * 100

Product C - Value at 2020.01.01 = 100.  Value at other dates = Value at that date/Value at 2020.01.01 * 100

 

Please can anyone show me how to create a measure or calculated column to support this base index calculation?

 

 

Thanks. 

1 ACCEPTED SOLUTION

Hi,

 

Please try this:

 

Measure = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                && 'Table'[Category] IN FILTERS ( 'Table'[Category] )
        )
    )
VAR b =
    IF (
        MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
        0,
        DIVIDE ( ( MAX ( 'Table'[value] ) - a ), a )
    )
VAR c =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[Date] = MAXX ( ALL ( 'Table' ), 'Table'[Date] ) )
    )
        / (
            CALCULATE (
                MAX ( 'Table'[value] ),
                FILTER ( 'Table', 'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] ) )
            )
        ) - 1
RETURN
    IF ( ISINSCOPE ( 'Table'[Date] ), b, c )

 

The result shows:

2.PNG

See my attached pbix file.

 

Best Regards,

Giotto

View solution in original post

10 REPLIES 10
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please take following steps:

1)Create a seperate slicer table:

SlicerTable = DISTINCT(SELECTCOLUMNS('Table',"Date",'Table'[Date]))

2)Try this measure:

Measure = 
IF (
    MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
    100,
    (
        MAX ( 'Table'[value] )
            / CALCULATE (
                MAX ( 'Table'[value] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                )
            )
    ) * 100
)

3)When choose one date in slicer, the result shows:

36.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

Anonymous
Not applicable

Hi @v-gizhi-msft 

 

Thanks for the suggestion. I think your solution solved 90% of the problem.

 

Looking at the table, the math is accurate for the column B. For column A and C, it is anchored based on 7952 (column B) as the starting for index.


For example, column A on 2020-01-02 should not be 52. It should be (4120/4200)*100 = 98.

Can you please help me double check the formula?

Thanks!

 

Hi,

 

Please try this measure:

Measure = 
IF (
    MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
    100,
    DIVIDE (
        MAX ( 'Table'[value] ),
        CALCULATE (
            MAX ( 'Table'[value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                    && 'Table'[Category] IN FILTERS ( 'Table'[Category] )
            )
        )
    ) * 100
)

The result shows:

6.PNG

See my attached pbix file.

 

Best Regards,

Giotto

Anonymous
Not applicable

Thanks @v-gizhi-msft ! That works!

 

Now I need to create a subcategory called Product 1,2,3 for Category A and then Product 3,4,5 for Category B

 

How could I create the indexes for both the Category and Products Column?

Let's say we want to analyze trend between Category A and B.  Then, we need to look into Category A to see which Product is trending better.

 

I tried to replace the Allselected from Category to Product but it doesn't pick up the product. It only shows 100 and then 0.01.

Hi,

 

Please try this:

Measure =
IF (
    MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
    100,
    DIVIDE (
        MAX ( 'Table'[value] ),
        CALCULATE (
            MAX ( 'Table'[value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                    && 'Table'[Category] IN FILTERS ( 'Table'[Category] )
                    && 'Table'[SubCategory] IN FILTERS ( 'Table'[SubCategory] )
            )
        )
    ) * 100
)

Hope this helps.

 

Best Regards,

Giotto

Anonymous
Not applicable

bar chart example.PNGThanks @v-gizhi-msft .

 

It does not work yet.....

This question goes back to the original question. I also put a bar chart in addition to a line chart to show % changes when indexing for a date. For example, table on the far right is showing % change for Cat A, B, C. However, the bar chart is only right for Category C.

Please can you let me know how to fix this formula to adapt to the bar chart?

Also, can we add a slicer for a date to end the calculation period so we can both adjust the starting indexing date and the ending calculation date.

 

Hi,

 

What are the expected results on this bar chart for Category A and B?

And please try this measure:

Measure =
IF (
    MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
    100,
    IF (
        ISINSCOPE ( 'Table'[SubCategory] ),
        DIVIDE (
            MAX ( 'Table'[value] ),
            CALCULATE (
                MAX ( 'Table'[value] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                        && 'Table'[SubCategory] IN FILTERS ( 'Table'[SubCategory] )
                )
            )
        ) * 100,
        IF (
            ISINSCOPE ( 'Table'[Category] ),
            DIVIDE (
                MAX ( 'Table'[value] ),
                CALCULATE (
                    MAX ( 'Table'[value] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                            && 'Table'[Category] IN FILTERS ( 'Table'[Category] )
                    )
                )
            ) * 100
        )
    )
)

 

Best Regards,

Giotto

Anonymous
Not applicable

@v-gizhi-msft 

 

The expected result for category A is 3% and Category  is 82% based on the latest data available (i.e.index on Jan 12 until jan 15)

Hi,

 

Please try this:

 

Measure = 
VAR a =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                && 'Table'[Category] IN FILTERS ( 'Table'[Category] )
        )
    )
VAR b =
    IF (
        MAX ( 'Table'[Date] ) = MAX ( SlicerTable[Date] ),
        0,
        DIVIDE ( ( MAX ( 'Table'[value] ) - a ), a )
    )
VAR c =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER ( 'Table', 'Table'[Date] = MAXX ( ALL ( 'Table' ), 'Table'[Date] ) )
    )
        / (
            CALCULATE (
                MAX ( 'Table'[value] ),
                FILTER ( 'Table', 'Table'[Date] = SELECTEDVALUE ( SlicerTable[Date] ) )
            )
        ) - 1
RETURN
    IF ( ISINSCOPE ( 'Table'[Date] ), b, c )

 

The result shows:

2.PNG

See my attached pbix file.

 

Best Regards,

Giotto

amitchandak
Super User
Super User

@Anonymous ,Try like

divide(sum(Table[Value]),sumx(filter(all(table), table[date] =date(2020,01,01)),Table[Value]))*100

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Fabric July 2025 Monthly Update Carousel

Fabric Monthly Update - July 2025

Check out the July 2025 Fabric update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors