topic Re: Returning the average of known values for all blank values in DAX Commands and Tips

Returning the average of known values for all blank values

fhoe — Fri, 10 Feb 2023 15:25:10 GMT

Hello,
I am looking for an expert that can help me with a dax formula to calculate the average of some known values in a column and then return that average in the rows where there is no value present.

I have a matrix visual with data similar to the below

The reason for why there are missing emission values is caused by a relationship between the emission and dimension table. I still want to calculate the total emissions (Emissions*Qty bought) for those rows where no emission data is available using the average emissions from the other suppliers.

SKU	Dimension	Emissions	Qty bought	Value to be returned	Total emissions
U123	Supplier 1	0,5	100	0,5	0,5*100
U123	Supplier 2		200	AVERAGE(0,5+0,6+0,7) = 0,6	0,6*200
U123	Supplier 3		300	AVERAGE(0,5+0,6+0,7) = 0,6	0,6*300
U123	Supplier 4	0,6	200	0,6	0,6*200
U123	Supplier 5	0,7	200	0,7	0,7*200

Re: Returning the average of known values for all blank values

Jihwan_Kim — Sat, 11 Feb 2023 05:03:40 GMT

Hi,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

Total emissions measure: = VAR _emissionavg = CALCULATE ( AVERAGE ( Data[Emissions] ), ALL ( Data[Dimension] ) ) RETURN SUMX ( SUMMARIZE ( Data, Data[SKU], Data[Dimension] ), CALCULATE ( IF ( ISBLANK ( MAX ( Data[Emissions] ) ), _emissionavg * SELECTEDVALUE( Data[Qty bought] ), SUMX( Data, Data[Emissions] * Data[Qty bought] ) ) ) )

Re: Returning the average of known values for all blank values

fhoe — Mon, 13 Feb 2023 11:24:00 GMT

Hi,

Appreciate the support!

I tried the DAX code and while it is correctly returning the total emissions for those rows where there is a value present, it shows nothing for blank rows.

The datamodel consists of four tables:
1. "Emission factor" where the column "emissions" comes from
2. "Item classification" that has a relationship to the SKU column from emisssion factor table
3. "PO data" that has a relationship to the SKU column from item classification table. PO data table provides the quantity in the table as a simple DAX measure.
4. "Dimension" which has a relationship to both PO data and emission factor through the rows in the dimension column.

The blank rows in the table are created by the relationship where there are no emission data for a given supplier, but where there are quantity data. It would be great if the measure could just return the average emissions for each SKU in the instances where there are no data in the emissions field. Like I pointed out in the table.

Please let me know if this makes sense. Thank you so much