DAX formula for calculating different indices inside one table

[BIfanatic]

Dear all,

I am looking for a way to extract different indices from one table. Consider the table at the bottom, with which I want to calculate two sales indices:

- A general index for each retailer, regardless of product (total MAT sales x 100 / total MAT-1 sales)

- An index for each product at each retailer  (MAT sales x 100 / MAT-1 sales)

What is the right DAX expression to do this? Is there a way to do this with one table, or do I need to create two tables? Right now, I can't find the right DAX expression and so far I ended up with indices having the exact same outcome.

Thanks in advance for your help!

Retailer   Product   Sales MAT  Sales MAT-1
1	Alpha	 36.765 	 33.067 
1	Bravo	 1.030.100 	 979.159 
1	Charlie	 1.627.002 	 1.529.596 
2	Alpha	 422.728 	 318.362 
2	Bravo	 1.475.902 	 1.616.456 
2	Charlie	 6.856.130 	 7.227.239 
3	Alpha	 173.111 	 140.780 
3	Bravo	 1.257.863 	 1.102.444 
3	Charlie	 2.480.445 	 2.804.763 
4	Alpha	 70.797 	 81.745 
4	Bravo	 607.618 	 564.895 
4	Charlie	 3.086.947 	 3.208.257 
5	Alpha	 90.355 	 83.760 
5	Bravo	 967.043 	 639.352 
5	Charlie	 2.484.531 	 2.416.448 

[v-ljerr-msft]

Hi @BIfanatic,

If I understand you correctly, you should be able to use the formulas below to create two calculate tables to get the indices for Retailer and Product in your scenario. 

Retailer with Indices =
SUMMARIZE (
    Table1,
    Table1[Retailer],
    "Indices for Retailer", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) )
)

Product with Indices =
SUMMARIZE (
    Table1,
    Table1[Product],
    "Indices for Product", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) )
)

Note: just replace Table1 with your real table name.

Regards

[BIfanatic]

@v-ljerr-msft Thanks for your input! The first index seems to work great.

The second is close but I need an index for each product and for each retailer. Your suggestion creates a table solely on product level. So, something like this:

Retailer     Product     Index
1                X       108
1                Z       118
2                Y       132
3                Y       122
3                Z       133

Do you know how to adjust the code you suggested? Thanks.

[v-ljerr-msft]

Hi @BIfanatic,

The formula below should work in that scenario. 

Product with Indices =
SUMMARIZE (
    Table1,
    Table1[Retailer],
    Table1[Product],
    "Indices for Product", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) )
)

Regards

[BIfanatic]

@v-ljerr-msft This is great, thanks a lot! Another question: is it possible to create a measured column inside an existing table, with which you can aggregate a measure based on a column inside that table?

[v-ljerr-msft · Accepted solution]

Hi @BIfanatic,

Yes, it is. For example, you can use the formula below to create a new calculate column in the existing table to calculate the Total Sales MAT of Current Retailer. 

Total Sales MAT of Current Retailer = 
CALCULATE (
    SUM ( Table1[Sales MAT] ),
    FILTER ( ALL ( Table1 ), Table1[Retailer] = EARLIER ( Table1[Retailer] ) )
)

Regards

[BIfanatic]

Dear all,

I am looking for a way to extract different indices from one table. Consider the table at the bottom, with which I want to calculate two sales indices:

- A general index for each retailer, regardless of product (thus: total MAT sales x 100 / total MAT-1 sales)

- An index for each product at each retailer (thus: MAT sales x 100 / MAT-1 sales)

What is the right DAX expression to do this? Is there a way to do this with one table, or do I need to create two tables? Right now, I can't find the right DAX expression and so far I ended up with indices having the exact same outcome.

Thanks in advance for your help!

Retailer  Product   Sales MAT	 Sales MAT-1
1	Alpha	 36.765 	 33.067 
1	Bravo	 1.030.100 	 979.159 
1	Charlie	 1.627.002 	 1.529.596 
2	Alpha	 422.728 	 318.362 
2	Bravo	 1.475.902 	 1.616.456 
2	Charlie	 6.856.130 	 7.227.239 
3	Alpha	 173.111 	 140.780 
3	Bravo	 1.257.863 	 1.102.444 
3	Charlie	 2.480.445 	 2.804.763 
4	Alpha	 70.797 	 81.745 
4	Bravo	 607.618 	 564.895 
4	Charlie	 3.086.947 	 3.208.257 
5	Alpha	 90.355 	 83.760 
5	Bravo	 967.043 	 639.352 
5	Charlie	 2.484.531 	 2.416.448