Calculate index base 100 for different products

[Anonymous]

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. 

[v-gizhi-msft]

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:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Anonymous]

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!

[v-gizhi-msft]

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:

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

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.

[v-gizhi-msft]

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]

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

[v-gizhi-msft]

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]

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

[v-gizhi-msft · 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:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

@Anonymous ,Try like

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