Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
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
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] )
)
)
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |