cancel
Showing results for
Did you mean:
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.

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

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

Regards

6 REPLIES 6
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.

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

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

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

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

## Re: DAX formula for calculating different indices inside one table

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

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

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

Regards

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

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

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors