Matrix Table - Results are not shown for all levels of hierarchy

[Marco2903]

Hi, I have an issue with the Matrix Table. I'm preparing a report based on Azure Portal exports and I wanted to calculate the Reserved Instances values on my own. The issue occurs when I'm trying to expand the row levels of the matrix table, please see screenshot below:

The calculation is right only for the highest level of hierarchy, however, when it goes deeper, it doesn't show any values (blue circles on the screenshot).

1 Year MonthlyBenefit = 
VAR _Benefit =
    SUM ( 'Usage Details'[Cost] ) - 'Usage Details'[1Y Monthly_Cost]
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Usage Details'[ResourceName]),
            IF (
                AND ( _Benefit > 0, 'Usage Details'[1Y Monthly_Cost] > 0 ),
                _Benefit,
                BLANK ()
            ),
        BLANK ()
    )

Above, you can find the calculation which I'm currently using. I've been trying to add some more ISINSCOPE elements, but unfortunately the results were wrong / not shown at all. 

Names of all 3 levels are: SubscriptionName, ResourceGroup and ResourceName.

Can someone give me a hint, how can I show the results for all levels of the hierarchy?
Thank you, 
BR,
Marco

[v-cgao-msft]

Hi @Marco2903 ,

Please test the calculation for each level of the hierarchy separately to determine where the problem occurs. 

Test if the condition after the if is False, causing it to consistently return a blank.

Best Regards,
Gao
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!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Marco2903]

Tried to use another ISINSCOPE instances, without success. Still struggling to get the right results. Does anyone have any idea how to fix it?

[dk_dk]

Hi @Marco2903 

The other levels are blank because of the ISINSCOPE limiting your calculation to the Resource Name level.

IF you simply want to aggregate the amounts on the higher levels of the hierarchy, then in theory you could just exclude the ISINSCOPE from the RETURN statement and it should work. 

1 Year MonthlyBenefit = 
VAR _Benefit =
    SUM ( 'Usage Details'[Cost] ) - 'Usage Details'[1Y Monthly_Cost]
RETURN
IF (
    AND ( _Benefit > 0, 'Usage Details'[1Y Monthly_Cost] > 0 ),
    _Benefit,
    BLANK ()
   )

Can you let me know if this works, and if not, what the issue is, and what the expected result should have looked like?


Br,
Daniel

[Marco2903]

Tried to implement your solution, unfortunately, nothing changes. I named it "MonthlyCost Check" Please take a look on the screenshot below:

The result is being shown only for Resource Name level (it's mentioned in the DAX formula, however, when I'm trying to replace Resource Name with any other column name, nothing changes).

 My expected solution would look like that: