cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MunroA7 Regular Visitor
Regular Visitor

Don't sum/calculate in measures when adjacent column is blank

Hello,

 

So I have an issue where a target field is populated before the sales data arrives at the start of the month.  This is skewing my results.  In a table this isn't too much of a problem because you can just filter out the row using 'is not blank' tick box and it will re-calculate the data.

 

Sales MTDBudget MTDVarianceVariance%
7090-20-22.2
5070-20-28.6
2560-35-58.3
 55-55-100.0
145275-130-47.27

 

My question is about the measures which I drop into cards.  How do you stop it counting the blank rows?  If the Sales MTD column/row is blank, I don't want it to add in values from the budget or variance columns into my measures either.  This is my DAX:

 

Sales MTD: 

CALCULATE(TOTALMTD([SQLSales],DateDimension[Date]),DateDimension[Date]<=TODAY())
 
Budget MTD: 
[Target MTD]*(DIVIDE([Workdays Passed CM],[Work Days CM],0))
 
Variance%MTD =
VAR __BASELINE_VALUE = [Budget MTD Calc]
VAR __VALUE_TO_COMPARE = [SQLSalesMTD]
RETURN
    IF(
        NOT ISBLANK(__VALUE_TO_COMPARE),
        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
    )
 
VarianceSQLMTD = ([SQLSalesMTD]-[Budget MTD Calc])

 

Any ideas would be really welcome.  Thank you.

 

1 REPLY 1
Community Support Team
Community Support Team

Re: Don't sum/calculate in measures when adjacent column is blank

HI @MunroA7 ,

Can you please share some sample data for test?

How to Get Your Question Answered Quickly
Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 33 members 838 guests
Please welcome our newest community members: