Forum Discussion

ponczula's avatar
ponczula
Frequent Visitor
2 years ago
Solved

Power BI Desktop - total row does not sum up

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:(
 
 
 
Need Help
  • ponczula's avatar
    ponczula
    2 years ago

    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!
     

3 Replies

  • ponczula's avatar
    ponczula
    Frequent Visitor
    2 years ago

    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!
     
  • MNedix's avatar
    MNedix
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.