Power BI Desktop - total row does not sum up

[ponczula]

Dear Members,

got stucked with what seems to be an easy ride, but apparently is above my skillset and somehow wasn't able to find out correct answer.

Case:

Have both LBE and AOP. Not all managers provided LBE numbers so when LBE is empty I want Power BI to take AOP figures - simple as that. As per below formulas that's how I'm calculating both - results can be seen on attached screenshot:

LBE = CALCULATE ( SUM ( 'DB'[USD] ), FILTER ( 'DB', 'DB'[Scenario] = "LBE" ) ) / 1000

LBE final = IF ( ISBLANK ( [LBE] ), [AOP], [LBE] )

Problem: my Total row in LBE final measure seems to be incorrect as it sums up only LBE values totally ignoring AOP numbers. Wasn't able to find correct formula to overcome this issue - please advise how to fix this. Have no idea how to fix this issue:(

[ponczula · Accepted solution]

Hi all,

I have sorted out the case by myself by wraping the entire calculation into another measure with SUMX & SUMMARIZE function which eventually picks the total row in correct manner.

In other words:

1. Frist - calculating LBE measure as stated earlier
2. Second - calculating LBE final measure as stated earlier
3. Third - calculating new measure LBE TOTAL as per below formula:

LBE TOTAL =

SUMX (

    SUMMARIZE ( 'DB', Regions[Region Name], "TOTAL", SUM ( 'DB'[USD] ) ),

    [LBE final]

)

Hope this helps community as well!

[Anonymous]

Hi, @ponczula 

The measure correctly calculates individual rows because it evaluates the condition of each row. However, for the total row, Power BI attempts to aggregate the results of the measure directly, rather than re-evaluating the criteria for the aggregated values. This often leads to unexpected results when using conditional logic in measures.
You might be able to try the following DAX:

LBE final = 
SUMX(
    VALUES('DB'[SomeUniqueIdentifier]), 
    IF(
        ISBLANK(CALCULATE(SUM('DB'[USD]), 'DB'[Scenario] = "LBE")),
        CALCULATE(SUM('DB'[USD]), 'DB'[Scenario] = "AOP"),
        CALCULATE(SUM('DB'[USD]), 'DB'[Scenario] = "LBE")
    )
) / 1000

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MNedix]

I tried replicating your issue but it works for me (see screenshot). However, I did not replicate your first column, I just entered the figures by hand. Perhaps you have a weird filtering or formatting in there.