Forum Discussion
DAX expression - How to calculate Weightage average based on a condition
Hello Community,
I have been scratching my head for many hours to get a DAX for the following Data table
| SKU | Date | Total Time | Reason | Availability | Performance |
| Apple | 12-12-23 | 34 | Break | 50% | 65% |
| Apple | 12-12-23 | 22 | Reduced speed | 45% | 83% |
| Banana | 12-12-23 | 42 | Reduced speed | 36% | 87% |
| Carrot | 12-12-23 | 54 | Break | 37% | 46% |
| Apple | 13-12-23 | 20 | Reduced speed | 65% | 44% |
| Banana | 13-12-23 | 22 | Reduced speed | 12% | 39% |
| Banana | 13-12-23 | 55 | Break | 35% | 44% |
| Carrot | 13-12-23 | 11 | Break | 43% | 77% |
| Carrot | 13-12-23 | 45 | Break | 29% | 61% |
| Apple | 14-12-23 | 11 | Break | 34% | 37% |
| Apple | 14-12-23 | 25 | Break | 80% | 63% |
| Banana | 14-12-23 | 45 | Break | 33% | 35% |
| Carrot | 14-12-23 | 39 | Break | 26% | 64% |
| Apple | 15-12-23 | 20 | Break | 75% | 72% |
| Banana | 15-12-23 | 33 | Break | 23% | 56% |
| Banana | 15-12-23 | 35 | Reduced speed | 34% | 78% |
| Carrot | 15-12-23 | 23 | Break | 23% | 38% |
| Carrot | 15-12-23 | 70 | Break | 26% | 66% |
I am trying to get weighted average (Availability) and Weighted Average (Performance) based on SKU or Date ( what ever i set as a filter). The result should only include "Break" as a Reason.
Ultimately i am trying to achieve the below Values (i dont need a table)
Weightage is Total time for the filter/ total time. For example 34/90=38%,11/90=12%
1) Filter at SKU Level
| Reason Filter : Break only | ||||||
| Availability | Performance | |||||
| Apple | 61.9% | 62.6% |
2) Filter at Date
| Reason Filter : Break only | ||||||
| Date | Availability | Performance | ||||
| 12-12-23 | 42.0% | 53.3% | ||||
| 13-12-23 | 33.1% | 54.5% |
9 Replies
To calculate the weighted average of Availability and Performance based on a condition (in this case, "Break") using DAX in Power BI, you can follow these steps:
Create a Measure for Total Time for 'Break' Reason:
TotalTime_Break =
SUMX(
FILTER(
YourTableName,
YourTableName[Reason] = "Break"
),
YourTableName[Total Time]
)Calculate the Weighted Average for 'Break' Reason:
For the Availability and Performance weighted average, use the following measures:
WeightedAvg_Availability =DIVIDE(CALCULATE(SUMX(FILTER(YourTableName,YourTableName[Reason] = "Break"),YourTableName[Total Time] * YourTableName[Availability]),ALL(YourTableName)),[TotalTime_Break])WeightedAvg_Performance =DIVIDE(CALCULATE(SUMX(FILTER(YourTableName,YourTableName[Reason] = "Break"),YourTableName[Total Time] * YourTableName[Performance]),ALL(YourTableName)),[TotalTime_Break])Using the Measures in Visuals:
If you want to see the weighted averages for each SKU with the 'Break' reason:
- Drag and drop the SKU into a table or matrix visualization.
- Drag the WeightedAvg_Availability and WeightedAvg_Performance measures into the values section of the visualization.
If you want to see the weighted averages for each Date with the 'Break' reason:
- Similarly, drag and drop the Date into a table or matrix visualization.
- Again, drag the WeightedAvg_Availability and WeightedAvg_Performance measures into the values section.
Remember to replace YourTableName with the actual name of your table in Power BI.
These DAX measures should give you the desired weighted averages based on the 'Break' reason either at the SKU level or Date level as per your filters in Power BI.
Super Guru's amitchandak Ahmedx lbendlin Please help
