- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Indexing Using DAX

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Indexing Using DAX

09-18-2023
03:44 AM

Hi Everyone!

I'm facing a challenge to create a company-wide Index using DAX in PowerBi.

Please refer below as my Purchase data.

Item Code | Receipt Date | Unit Price | Qty | Value | UOM | |||||

ID_1 | 01-Apr-24 | 10 | 100 | 1000 | KG | |||||

ID_1 | 02-Apr-24 | 11 | 100 | 1100 | KG | |||||

ID_3 | 05-Apr-24 | 65000 | 20 | 1300000 | TON | |||||

ID_2 | 02-May-24 | 120 | 10000 | 1200000 | ECH | |||||

ID_1 | 05-May-24 | 14 | 220 | 3080 | KG | |||||

ID_2 | 10-May-24 | 160 | 3000 | 480000 | ECH | |||||

ID_3 | 18-May-24 | 67000 | 10 | 670000 | TON | |||||

ID_2 | 06-Jun-24 | 130 | 15000 | 1950000 | ECH | |||||

ID_1 | 07-Jun-24 | 20 | 300 | 6000 | KG | |||||

ID_2 | 09-Jun-24 | 135 | 1000 | 135000 | ECH | |||||

ID_2 | 12-Jul-24 | 155 | 23000 | 3565000 | ECH | |||||

ID_3 | 21-Jul-24 | 62000 | 23 | 1426000 | TON |

The output that is expected would look be Weighted Average of the Index that would be calculated.

Step 1: Weighted Average Matrix. (Simple Weighted Average of an Item for that month)

Apr-24 | May-24 | Jun-24 | Jul-24 | |||||

ID_1 | 10.5 | 14 | 20 | |||||

ID_2 | 129.231 | 130.3125 | 155 | |||||

ID_3 | 65000 | 67000 |
| 620 |

Step 2: Index of that Weighted Average. (Using base as 100, we calculate the Index100. Challenge is to make the first nonblank month as 100)

Apr-24 | May-24 | Jun-24 | Jul-24 | ||||||

ID_1 | 100 | 133.3333 | 190.4762 | ||||||

ID_2 | 100 | 100.8369 | 119.9403 | ||||||

ID_3 | 100 | 103.0769 | 95.38462 |

Step 3: Weighted Average of the Index (Now the last part to calculate the index of the entire company. Taking weights as the sum of value for that paticular month and item, we calculate Weighted Average) i.e. SUMX(Index*Value)/SUM(Value).

(For e.g. April-24 would be =((100*2100+100*1300000)/(2100+1300000)

and May-24 would be =((133.33*3080+100*1680000+103.0769*670000)/(3080+1680000+670000) and so on...

Apr-24 | May-24 | Jun-24 | Jul-24 | |||||

ID_Index | 100 | 100.9197 | 101.0941 | 112.9244 |

I'm facing difficulty when there is no entry for a paticular month, then the values become infinity.

Thanks,

Best Regards.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-18-2023
06:24 AM

To achieve the desired result of calculating a company-wide index in Power BI using DAX, you can follow these steps. The key challenge is handling cases where there is no entry for a particular month, which can result in division by zero errors. To overcome this challenge, you can use conditional logic to check for valid data before performing calculations.

Here's a step-by-step approach:

**Step 1: Weighted Average Matrix**

To calculate the weighted average matrix for each item and month, you can use DAX measures like this:

WeightedAverage = VAR TotalValue = SUM('PurchaseData'[Value]) RETURN SUMX('PurchaseData', [Unit Price] * [Qty] / TotalValue )

Create one measure like this for each month. These measures will calculate the weighted average for each item and month.

**Step 2: Index of Weighted Average**

To calculate the index based on the weighted averages with the first non-blank month as 100, you can use the following DAX measure:

Index100 = VAR FirstNonBlankMonth = CALCULATE( MIN('Calendar'[Month]), FILTER( ALL('Calendar'), [WeightedAverage] > 0 ) ) RETURN IF(ISBLANK([WeightedAverage]), BLANK(), [WeightedAverage] / CALCULATE([WeightedAverage], 'Calendar'[Month] = FirstNonBlankMonth) )

This measure calculates the index based on the weighted average, and it checks for a non-blank weighted average to avoid division by zero.

**Step 3: Weighted Average of the Index**

To calculate the company-wide weighted average of the index, you can use this DAX measure:

CompanyIndex = VAR TotalValue = SUM('PurchaseData'[Value]) RETURN SUMX(VALUES('PurchaseData'[Item Code]), [Index100] * [Value] / CALCULATE(SUM('PurchaseData'[Value]), VALUES('PurchaseData'[Item Code])) )

This measure calculates the weighted average of the index for the entire company while considering valid data points. It divides the sum of the product of index and value by the sum of the value for each item.

By using these measures, you should be able to calculate the weighted average matrix, the index based on the first non-blank month as 100, and the company-wide weighted average of the index while handling cases where there is no entry for a particular month without resulting in infinite values.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-21-2023
02:25 AM

Dear 123abc,

Thank you for the solution provided.

The first 2 steps are working as per your formula.

Although the formula for the last step is not working. The solution provided simply sums the enitre index as per VALUES(Itemcode).

I have tried using ALLExcept but it contradicts with the VALUES function.

If you can guide if it is possible with DAX, it would be helpful.

Thanks and Regards,

Yash Dhadke

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-22-2023
01:03 AM

To calculate the company-wide index correctly while considering individual items, you can use the following DAX measure:

Company Index =

VAR TotalValue = SUM('Purchase Data'[Value])

RETURN

DIVIDE(

SUMX(

ADDCOLUMNS(

SUMMARIZE('Purchase Data', 'Purchase Data'[Receipt Date]),

"WeightedIndex",

[Index100] * SUM('Purchase Data'[Value])

),

[WeightedIndex]

),

TotalValue,

0

)

This measure calculates the company-wide index by first creating a table that summarizes the data for each unique receipt date, calculates the weighted index for each date, and then sums up these weighted indexes. This approach ensures that the index is correctly calculated for each unique receipt date and then aggregated for the company-wide index.

With this measure, your company-wide index should be correctly calculated, taking into account individual items and their respective values for each month.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-25-2023
03:09 AM

Hi 123abc,

We have implemented your solution, but still are not able to get the desired result as the formula is not able to evaluate VALUES() function and populate a company wide index.

Please refer below screenshot.

Thank you for your help

Regards.

Announcements

Check out the September 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors

User | Count |
---|---|

25 | |

18 | |

18 | |

18 | |

17 |

Top Kudoed Authors

User | Count |
---|---|

33 | |

25 | |

18 | |

15 | |

13 |