find weekly variance of heirarchy measure at category level and sub-category

[praveenlc]

Hello, I am trying to find weekly varaince at each category level and sub-category level (CurrentWeek-PreviousWeek)/PreviousWeek

The challange is when I hide 0 values from the metrix table the calculation gives different results, I have used FILTER(), ISINSCOPE() and ALLEXCEPT to achieve the measures

In my example:

- for category 'cbf-cloudservices-prd' previous week=6770.06 and current week=1182.48 and varaince is -82.53%

- but when I apply variance <> blank(), then the values change to subcategory values (4613.13, 745.20, 83.85%)

attaching the image and dax measure, could someone please help me undertand fix the problem.

MEASURE1:_CurrWeek AZ cost= SWITCH(TRUE(), ISINSCOPE('df Usage'[MeterCategory]),

CALCULATE (

    [Azure Cost],

    FILTER (

        ALL ( MasterCalendar ),

                 MasterCalendar[YearWeek]= MAX ( MasterCalendar[YearWeek] )

    ))

, ISINSCOPE(SingleSourceOfTruth_Actuals[SubscriptionName]),

CALCULATE (

    [Azure Cost],

    FILTER (

        ALL ( MasterCalendar ),

MasterCalendar[YearWeek]= MAX ( MasterCalendar[YearWeek] )

    ),ALLEXCEPT(SingleSourceOfTruth_Actuals,SingleSourceOfTruth_Actuals[SubscriptionName])),0)

MEASURE2:_PrevWeek AZ cost1 = SWITCH(TRUE(), ISINSCOPE('df Usage'[MeterCategory]),

CALCULATE (

    [Azure Cost],

    FILTER (

        ALL ( MasterCalendar ),

           MasterCalendar[YearWeek] = MAX ( MasterCalendar[YearWeek] ) - 1))

, ISINSCOPE(SingleSourceOfTruth_Actuals[SubscriptionName]),

CALCULATE (

    [Azure Cost],

    FILTER (

        ALL ( MasterCalendar ),

            MasterCalendar[YearWeek] = MAX ( MasterCalendar[YearWeek] ) - 1

    ),ALLEXCEPT(SingleSourceOfTruth_Actuals,SingleSourceOfTruth_Actuals[SubscriptionName])),0)

MEASURE3VARIANCE:_Weekly Variance Azure Cost 1 % =

IF (

    [_CurrWeek AZ cost] > 500

        || ([_CurrWeek AZ cost] > [_PrevWeek AZ cost1]

        && [_PrevWeek AZ cost1] > 200),

    DIVIDE (

        [_CurrWeek AZ cost]-[_PrevWeek AZ cost1],

        CALCULATE([_PrevWeek AZ cost1]),0))

[v-xuxinyi-msft]

Hi @praveenlc 

Can you provide the exact data and data structure? How to provide sample data in the Power BI Forum - Microsoft Fabric Community

We can better understand the problem and help you. Or Show them as screenshots. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

[praveenlc]

how do I exclude the blank rows (red marked) without changing the yellow highlighted category values, filters on subcategory should not effect the values from category

[praveenlc]

any help would be greatly appreciated, please let me know if any more details required

Thanks in advance