Forum Discussion

champioz's avatar
champioz
Frequent Visitor
2 years ago
Solved

Measure totals problem with specific row exclusions

Hi there,

I have a complicated problem with measure totals in a hierarchical row context.

 

Problem:

I have two measures (say for sake of example VAL_ONE and VAL_TWO)

I built a view that displays VAL_ONE  for accounts that report VAL_ONE, and VAL_TWO for all others

Accounts are grouped into regions and the row totals for regions works how I would hope using this implementation:

 

IF(
    HASONEFILTER('Account'[Name]),
    IF(
        ISBLANK([VAL_ONE]),
        [VAL_TWO],
        [VAL_ONE]),
    SUMX(FILTER('Account', ISBLANK([VAL_ONE])), [VAL_TWO]) + SUMX(FILTER('Account',NOT(ISBLANK([VAL_ONE]))),[VAL_ONE]))
 
The SUMX handles the regional row totals. This works great for the naive case I originally had in mind where the two numbers are always present if they're going to be.
But now there's a wrinkle -- some accounts lag their VAL_ONE reporting by a month. So during that month, this checks if VAL_ONE  is empty (it is) and reports their VAL_TWO. But we never want to see VAL_TWO from those accounts in this combo measure.
 
This is very easy to fix on the per-account level -- I check if Account[Name] has ever reported VAL_ONE, for example, and if so prevent it from adding their VAL_TWO to the combo measure. Likewise I can simply hardcode some account name exclusions.
 
Both of these approaches fail at the regional row level -- when using names, it cannot smartly check the name of each separate account it simply adds up for the whole region, and when using past reported VAL_ONE it over-corrects and cancels out all VAL_TWO:

See also attached PBIX 

 

Desired output:

 

 

Does anyone know of an alternate approach to work for this case? Maybe I'm missing something obvious

 

PBIX

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi champioz ,

     

    I interpreted your problem description and made the following attempts:

     

    Firstly, change the original measure to:

     

    Combo Measure that Favors VAL_ONE, Excludes Acc One =
VAR _past_reported_val =
    CALCULATE ( [VAL_ONE_TOTAL], ALL ( Dates ) )
RETURN
    IF (
        HASONEFILTER ( 'Accounts'[Name] ),
        IF (
            AND ( ISBLANK ( [VAL_ONE_TOTAL] ), _past_reported_val = 0 ),
            [VAL_TWO_TOTAL],
            [VAL_ONE_TOTAL]
        )
    )

     

    Then create a new measure and achieve your requirements:

     

    MEASURE =
IF (
    HASONEFILTER ( Accounts[Name] ),
    'VAL_ONE'[Combo Measure that Favors VAL_ONE, Excludes Acc One],
    SUMX (
        ALLEXCEPT ( 'Accounts', Accounts[Region] ),
        [Combo Measure that Favors VAL_ONE, Excludes Acc One]
    )
)

     

    Finally,  you can get the correct result at the row level:

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi champioz ,

     

    I interpreted your problem description and made the following attempts:

     

    Firstly, change the original measure to:

     

    Combo Measure that Favors VAL_ONE, Excludes Acc One =
VAR _past_reported_val =
    CALCULATE ( [VAL_ONE_TOTAL], ALL ( Dates ) )
RETURN
    IF (
        HASONEFILTER ( 'Accounts'[Name] ),
        IF (
            AND ( ISBLANK ( [VAL_ONE_TOTAL] ), _past_reported_val = 0 ),
            [VAL_TWO_TOTAL],
            [VAL_ONE_TOTAL]
        )
    )

     

    Then create a new measure and achieve your requirements:

     

    MEASURE =
IF (
    HASONEFILTER ( Accounts[Name] ),
    'VAL_ONE'[Combo Measure that Favors VAL_ONE, Excludes Acc One],
    SUMX (
        ALLEXCEPT ( 'Accounts', Accounts[Region] ),
        [Combo Measure that Favors VAL_ONE, Excludes Acc One]
    )
)

     

    Finally,  you can get the correct result at the row level:

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!