topic Re: delete values of a measure when a value of an other measure is blank in DAX Commands and Tips

delete values of a measure when a value of an other measure is blank

Anonymous — Fri, 28 Jun 2024 09:03:15 GMT

Hello, I wrote this measure to calculate sales on square meters

Sold/Sqm = Sold/SqMeters

but when I try to average the total of this measure by Store, he gets the SqMeters total wrong because he returns the SqMeters value even when the Sold value related to a Store, is empty.

Store is a column
Sold is a measure
SqMeters is a column
Sold/SqM is a measure

Red is the wrong total, and green is the right onw that i want because doesn't count FOOD store that has empty sold.

How do I change the measure so that it only counts the Solds present related to Store?

I also wrote
Sold/Sqm =
VAR _soldsq = Sold/SqMeters
RETURN
IF([Sold],_soldsq)
or
Sold/Sqm =
VAR _soldsq = Sold/SqMeters
RETURN
IF([Sold]>1,_soldsq)

or
Sold/Sqm =
VAR _soldsq = Sold/SqMeters
RETURN
IF(NOT(ISBLANK([Sold]>1)),_soldsq)

but it doesn't work

Thank you!

Re: delete values of a measure when a value of an other measure is blank

xifeng_L — Fri, 28 Jun 2024 11:13:36 GMT

Hi Anonymous

You can try below measure.

Sold/Sqm = CALCULATE ( Sold / SqMeters, FILTER ( VALUES ( Table[Store] ), [Sold] <> BLANK () ) )

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: delete values of a measure when a value of an other measure is blank

Anonymous — Fri, 28 Jun 2024 15:19:43 GMT

thank you so much, it works!!