CODEWITHSUNDEEP
powerbidax
TJB
TJB

Reputation: 877

Remove blanks from DAX average total

I have a table called Conditions which links to a table called Agreement, relationship on AgreementID.

In Conditions, I have two measures already pre-calculated in the model called Margin Pct-EOP and Minimum Pct-EOP which return a value based on the filter context.

I have created measure in Power BI called InterestRateFloorBoolean which returns true if those measures don't match. 

InterestRateFloorBoolean = if('Conditions'[Margin Pct-EOP] <> 'Conditions'[Minimum Pct-EOP],true,false)

If that measure returns true, then a subsequent measure called InterestRateFloorAmount runs the below calculation (a simple floored subtraction).

if([InterestRateFloorBoolean] = true,floor([Minimum Pct-EOP] - [Margin Pct-EOP],0.01),BLANK())

At a row level, the desired output is returned, blank() showing where the Margin/Minimum measures match. However, the Total runs an average of 2 AgreementID entries as ID 3236 does have Margin and Minimum values. ID 3336 is excluded as it does not have any entries.

I get that blank() is not the same as null and is essentially being treated like a 0 for the purposes of the Total (average). How can I construct my InterestRateFloorAmount measure so that when the total is averaged it does not include any blank() values?

Current output by way of illustration, the Total for InterestRateFloorAmount should be 4.00% (the percentage is a format):

enter image description here

Upvotes: 0

Views: 1301

Answers (1)

OscarLar
OscarLar

Reputation: 1335

This is due to calculation context and how the two measures [Margin Pct-EOP] and [Minimum Pct-EOP] are calculated.

Because in the context of the the total line the calculation it ignores the context of the previous lines. Hence InterestRateFloorAmount compares the two measures in the IF-statement:

InterestFloorAmount = 
IF(
    [InterestRateFloorBoolean] = true,
    floor([Minimum Pct-EOP] - [Margin Pct-EOP], 0.01),
    BLANK()
)

however in the context of Total the two measures evaluate to 4.20% and 2.20% (these, I assume, are calculated like (2.25+6.15)/2 and (2.25+2.15)/2 respectively). Thus InterestFloorBoolean evaluates to TRUE and therefore InterestRateFloorAmount calculates 4.20%-2.20% and you end up with 2.00%.

Upvotes: 1

Related Questions