topic Re: DAX expression - How to calculate Weightage average based on a condition in DAX Commands and Tips

DAX expression - How to calculate Weightage average based on a condition

wasirafi — Tue, 26 Dec 2023 02:06:18 GMT

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

Re: DAX expression - How to calculate Weightage average based on a condition

123abc — Tue, 26 Dec 2023 10:58:37 GMT

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.

Re: DAX expression - How to calculate Weightage average based on a condition

wasirafi — Wed, 27 Dec 2023 15:08:44 GMT

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

Re: DAX expression - How to calculate Weightage average based on a condition

wasirafi — Thu, 28 Dec 2023 01:56:49 GMT

Super Guru's amitchandak Ahmedx lbendlin Please help

Re: DAX expression - How to calculate Weightage average based on a condition

lbendlin — Thu, 28 Dec 2023 02:12:01 GMT

Re: DAX expression - How to calculate Weightage average based on a condition

wasirafi — Thu, 28 Dec 2023 03:40:16 GMT

You are a Genius!!!!🙂

Re: DAX expression - How to calculate Weightage average based on a condition

wasirafi — Thu, 25 Apr 2024 21:14:50 GMT

lbendlin

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

Re: DAX expression - How to calculate Weightage average based on a condition

lbendlin — Fri, 26 Apr 2024 00:47:21 GMT

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.

Re: DAX expression - How to calculate Weightage average based on a condition

wasirafi — Fri, 26 Apr 2024 04:43:34 GMT

lbendlin ,

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.

Re: DAX expression - How to calculate Weightage average based on a condition

lbendlin — Fri, 26 Apr 2024 10:56:34 GMT

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.