cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BIfanatic Resolver I
Resolver I

DAX formula for calculating different indices inside one table

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: DAX formula for calculating different indices inside one table

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

c1.PNG

 

Regards

View solution in original post

6 REPLIES 6
BIfanatic Resolver I
Resolver I

DAX expression for calculating different indices with one table

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

Re: DAX formula for calculating different indices inside one table

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. Smiley Happy

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.

 

t2.PNG

 

Regards

BIfanatic Resolver I
Resolver I

Re: DAX formula for calculating different indices inside one table

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

 

Microsoft
Microsoft

Re: DAX formula for calculating different indices inside one table

Hi @BIfanatic,

 

The formula below should work in that scenario. Smiley Happy

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

t3.PNG

 

Regards

BIfanatic Resolver I
Resolver I

Re: DAX formula for calculating different indices inside one table

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

Microsoft
Microsoft

Re: DAX formula for calculating different indices inside one table

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

c1.PNG

 

Regards

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors