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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
curtismob
Helper IV
Helper IV

Exclude Blank Measure Values From Average Calculation

Hello, I am looking for some direction on excluding blank measure values from my average cost per square foot calculation.  I am not using AVERAGE, because I have to do the cost per sqft calculation first.  See example below.  The desired result would be for the total average to be 10.00, but the Division 1/Product A Janurary thru June months w/no data, is causing the total average to be 7.50 instead.  This is all driven by the date slicer.  If the slicer is changed to 7/1/2020 thru 12/31/2020, then it works as ecpected with a total average of 10.00.  The below is a simplified version of my actual data and I have the pbix for the example below, I'm just not sure how to upload or link it.  I provided the simple table of data, along with my calculated columns and measures.

 

Any help would be greatly appreciated, 

@curtismob

 

DivisionProductSQFTYearMonthCost
Division 1ProductA102020 Jul100
Division 1ProductA102020 Aug100
Division 1ProductA102020 Sep100
Division 1ProductA102020 Oct100
Division 1ProductA102020 Nov100
Division 1ProductA102020 Dec100
Division 2ProductB102020 Jan100
Division 2ProductB102020 Feb100
Division 2ProductB102020 Mar100
Division 2ProductB102020 Apr100
Division 2ProductB102020 May100
Division 2ProductB102020 Jun100
Division 2ProductB102020 Jul100
Division 2ProductB102020 Aug100
Division 2ProductB102020 Sep100
Division 2ProductB102020 Oct100
Division 2ProductB102020 Nov100
Division 2ProductB102020 Dec100

 

Calulated columns and measures:

_cArea = "Area 1"  (This was added to the table after the fact, to add a level to the heirarchy)
_cCost Per Sqft = DIVIDE(CostPerSqft[Cost], CostPerSqft[SQFT], 0)
_cYYYY MonFORMAT(CostPerSqft[YearMonth].[Date], "YYYY MMM")
_cYYYYMMFORMAT(CostPerSqft[YearMonth].[Date], "YYYYMM")
_mDistinctCount YYYMMDISTINCTCOUNT('CostPerSqft'[_cYYYY Mon])
_mCost Per SQFT =
VAR
BegSnapshotDate = MIN(CostPerSqft[YearMonth].[Date])
VAR
EndSnapshotDate = MAX(CostPerSqft[YearMonth].[Date])
RETURN
CALCULATE(SUM('CostPerSqft'[_cCost Per SQFT]),
FILTER('CostPerSqft',
'CostPerSqft'[YearMonth].[Date] >= BegSnapshotDate &&
'CostPerSqft'[YearMonth].[Date] <= EndSnapshotDate))
_mAvg Cost Per SQFT per Product =
CALCULATE(DIVIDE(DIVIDE('CostPerSqft'[_mCost Per SQFT],'CostPerSqft'[_mDistinctCount YYYMM], 0), DISTINCTCOUNT('CostPerSqft'[Product]), 0))
 

 

curtismob_1-1608647824018.png

 

 

18 REPLIES 18
v-shex-msft
Community Support
Community Support

HI @curtismob,

Did Ashish_Mathur's formula help for your scenario? If this is a case, you can consider accepting his suggestion to help others who faced a similar requirement to find it more quickly.

If not, you can feel free to post here with detailed descriptions to help us clarify your scenario.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

Upload your PBI file to Google Drive and share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish,

Thank you for responding, please see link below.

https://drive.google.com/file/d/1v01ZpHo2XRy3LJSODzDEsOfXlF2iYsZp/view?usp=sharing 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

Thanks again for the response.  I used very simple data for my example, possibly too simple.  Unfortunately, if the data is changed by adding another product in each divison and both cost and square feet numbers are changed,so that all cost per sqft doesn't come out to 10.00, then the suggested changes do not appear to work.

 

Total area = SUM(CostPerSqft[SQFT])
Total cost = SUM(CostPerSqft[Cost])
Cost per square foot = DIVIDE([Total cost],[Total area])
 
The average totals appear to be close, but are still off.
 

Hi,

How would you expect me to help you if you do not share a representative dataset?  Share a realistic dataset and show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I apologize for the simplified data.  Below are links to my version with updated data and your version with the updated data.

 

Ashish version:
https://drive.google.com/file/d/1CkvrqfaEoJv-ac-3cE4I8y8ehs-P-EvB/view?usp=sharing 

Curtis version:
https://drive.google.com/file/d/1b_WtRzddQylXohYGQV3aM8EEdpxsI4Yz/view?usp=sharing 

 

 

Hi,

I receive an access desnied message when i click on those links.  On any dataset that you share, please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

In my version of the file, the average is 25.28.  That seems correct.  Drag the other 2 measures to the visual and export the data to MS Excel.  Add the Total cost and Total Area column and divide the 2.  The result will be 25.28.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Please recall my original post:

 

_mAvg Cost Per SQFT per Product =
CALCULATE(DIVIDE(DIVIDE('CostPerSqft'[_mCost Per SQFT],'CostPerSqft'[_mDistinctCount YYYMM], 0), DISTINCTCOUNT('CostPerSqft'[Product]), 0))
 
Unfortunately, your solution does not take into account the separate Products.

It does.  Please recheck.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for your efforts on this.

v-shex-msft
Community Support
Community Support

Hi @curtismob,

I think this may be related to your measure formula who setting the 0 in divide functions. You can add an if statement to check the current row count to confirm they not blank.

Handling BLANK in DAX 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft,

 

Thank you for the response.  Can you provide an "if" statement example using the measures I provide initially?  Everything I have tried doesn't appear to work.

 

Thank you,

Curtis

HI @curtismob,

You can try to use the following measure formula, I added the if statement to check hierarchy level and write a formula for the calculation on measure total level:

_mAvg Cost Per SQFT per Product = 
VAR detailLevel =
    CALCULATE (
        DIVIDE (
            DIVIDE ( [_mCost Per SQFT], [_mDistinctCount YYYMM], 0 ),
            DISTINCTCOUNT ( [Product] ),
            0
        )
    )
VAR totalLevel =
    DIVIDE (
        CALCULATE (
            SUM ( 'CostPerSqft'[_cCost Per SQFT] ),
            ALLSELECTED ( 'CostPerSqft' )
        ),
        SUMX (
            SUMMARIZE (
                CostPerSqft,
                [Area],
                [Division],
                [Product],
                "DC", DISTINCTCOUNT ( CostPerSqft[_cYYYY Mon] )
            ),
            [DC]
        ),
        0
    )
RETURN
    IF (
        ISINSCOPE ( CostPerSqft[_cYYYY Mon] ),
        detailLevel,
        IF ( ISINSCOPE ( CostPerSqft[Division] ), detailLevel, totalLevel )
    )

1.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@curtismob , This formula seems fine, remove 0 from first divide and check

CALCULATE(DIVIDE(DIVIDE([_mCost Per SQFT],[_mDistinctCount YYYMM]), DISTINCTCOUNT('CostPerSqft'[Product]), 0))

@amitchandak, thank you for the response.  Unforrtunately, that did not resolve my issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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