Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
fhoe
Regular Visitor

Returning the average of known values for all blank values

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.

SKUDimensionEmissionsQty boughtValue to be returnedTotal emissions
U123Supplier 10,51000,50,5*100
U123Supplier 2 200AVERAGE(0,5+0,6+0,7) = 0,60,6*200
U123Supplier 3 300AVERAGE(0,5+0,6+0,7) = 0,60,6*300
U123Supplier 40,62000,60,6*200
U123Supplier 50,72000,70,7*200
2 REPLIES 2
fhoe
Regular Visitor

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

Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1676091788611.png

 

 

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] )
            )
        )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.