Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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% |
https://1drv.ms/x/s!AjIGh95acxebg6F7lBBXTOoz7qe0rw?e=AIlOND
Solved! Go to Solution.
Can you pelase support here again....
Using the DAX your shared i end with many NaN ....how do fix this...
FYI i am using the divide function
https://1drv.ms/u/s!AjIGh95acxebg6tTfM75XKf7YlPPXw?e=3j9j3p
https://1drv.ms/x/s!AjIGh95acxebg6tU03koymicjRCeXQ?e=q9kFzD
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.
You provided way too much data. Cut the problem down to the essentials and fix that first.
Dax is a weakness for me. What would u suggest the new Dax should be that captures the solution i want ( as shared in the excel). We dont aggregate at "location name" alone -its a straight value. Any aggregation above location name wether its Area name or site name etc should be weighted average( based on the total time for oee2 = sum of upseconds+downseconds)
I intentionally provided more data ( more rows of data) to ensure the NaN issues for all rows is sorted.
The NaN is caused by your attempt to measure measures. Use SUMMARIZE to materialize the measures, and then SUMX.
Please explain the weighting logic again.
You are a Genius!!!!🙂
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:
Using the Measures in Visuals:
If you want to see the weighted averages for each SKU with the 'Break' reason:
If you want to see the weighted averages for each Date with the 'Break' reason:
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.
@123abc Thank you for sharing this, Unfortunately i am not getting the desired result Please See Pic from Power BI Versus what was expected( manual calculation in the excel sheet shared earlier). The sum of the weighted average cannot be more than 100% .
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |
User | Count |
---|---|
58 | |
23 | |
22 | |
19 | |
13 |