Forum Discussion

Petr_M's avatar
Petr_M
Frequent Visitor
2 years ago
Solved

In a Matrix hierarchy, alter one node to a desired value

I have a matrix of sales in a hierarchy. For the SubCategory x, I want to correct the value 16 to a more up-to-date 24. So I want to normalize that node and all its children by a factor 24/16. I thi...
DAX Hierarchy
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Petr_M ,
    Use the All function to ignore any filters that might have been applied during calculate 

    denominator.

    MeasureTotal =
    VAR Denominator=CALCULATE([RWA Digit Actual],
    FILTER(
    ALL('digit_database'),
    'digit_database'[NWU]= "B" && 'digit_database'[RWA_TYPE] = "X"
    ))
    VAR Ratio = 24/Denominator
    VAR TotalSales=SUMX(
    SUMMARIZE(
    digit_database,
    digit_database[NWU],digit_database[RWA_TYPE],
    "CalculatedValue",
    IF(
    SELECTEDVALUE(digit_database[NWU]) = "B",
    SWITCH(digit_database[RWA_TYPE],"X",
    SUMX(
    FILTER(digit_database, digit_database[NWU] = "B" ),
    [RWA Digit Actual] * Ratio
    ),[RWA Digit Actual]),
    SUMX(
    FILTER(digit_database, digit_database[NWU] <> "B"),
    [RWA Digit Actual]
    )
    )
    ),
    [CalculatedValue]
    )
    RETURN TotalSales

    Another simple measure for your reference:
    Measure =
    VAR Denominator = CALCULATE(
       [RWA Digit Actual],
       FILTER(
           ALL('digit_database'),
           'digit_database'[NWU] = "B" && 'digit_database'[RWA_TYPE] = "X"
       )
    )
    VAR Ratio = 24 / Denominator
    RETURN SUMX(
       ADDCOLUMNS(
           digit_database,
           "CalculatedValue",
           IF(
               [NWU] = "B" && [RWA_TYPE] = "X",
               [RWA Digit Actual] * Ratio,
               [RWA Digit Actual]
           )
       ),
       [CalculatedValue]
    )
    Result:

     

    Best regards,

     

    Joyce

     

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Petr_M ,
Thank you for your sample pbix file, please try following measure to check the result:

 

MeasureTotal = 
VAR Denominator=CALCULATE([RWA Digit Actual],
    FILTER(
        'digit_database',
        'digit_database'[NWU]= "B" && 'digit_database'[RWA_TYPE] = "X"
    ))
VAR Ratio = 24/Denominator

VAR TotalSales=SUMX(
   SUMMARIZE(
       digit_database,
       digit_database[NWU],digit_database[RWA_TYPE],
       "CalculatedValue",
       IF(
           SELECTEDVALUE(digit_database[NWU]) = "B",
           SWITCH(digit_database[RWA_TYPE],"X",
           SUMX(
               FILTER(digit_database, digit_database[NWU] = "B"  ),
               [RWA Digit Actual] * Ratio
           ),[RWA Digit Actual]),
           SUMX(
               FILTER(digit_database, digit_database[NWU] <> "B"),
               [RWA Digit Actual]
           )
       )
   ),
   [CalculatedValue]
)
RETURN TotalSales

 

Best regards,

Joyce

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

Petr_M's avatar
Petr_M
Frequent Visitor
2 years ago

Thank you, I really appreciate the time you put into this. That's some advanced stuff, I wouldn't be able to come up with.
At a first glance, however, the desired B/X sum seems to be 24.09 rather than 24.00. And the

[RWA Digit Actual] * 3

part looks a bit suspicious to me.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Petr_M ，


    Sorry for forgetting to change the test data, I've updated the above reply to the correct version.

    Best regards,

     

    Joyce

     

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

    • Petr_M's avatar
      Petr_M
      Frequent Visitor
      2 years ago

      I tried to change the 3 to Denominator before, but this leads to everything under X being 24.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Petr_M ,
        Use the All function to ignore any filters that might have been applied during calculate 

        denominator.

        MeasureTotal =
        VAR Denominator=CALCULATE([RWA Digit Actual],
        FILTER(
        ALL('digit_database'),
        'digit_database'[NWU]= "B" && 'digit_database'[RWA_TYPE] = "X"
        ))
        VAR Ratio = 24/Denominator
        VAR TotalSales=SUMX(
        SUMMARIZE(
        digit_database,
        digit_database[NWU],digit_database[RWA_TYPE],
        "CalculatedValue",
        IF(
        SELECTEDVALUE(digit_database[NWU]) = "B",
        SWITCH(digit_database[RWA_TYPE],"X",
        SUMX(
        FILTER(digit_database, digit_database[NWU] = "B" ),
        [RWA Digit Actual] * Ratio
        ),[RWA Digit Actual]),
        SUMX(
        FILTER(digit_database, digit_database[NWU] <> "B"),
        [RWA Digit Actual]
        )
        )
        ),
        [CalculatedValue]
        )
        RETURN TotalSales

        Another simple measure for your reference:
        Measure =
        VAR Denominator = CALCULATE(
           [RWA Digit Actual],
           FILTER(
               ALL('digit_database'),
               'digit_database'[NWU] = "B" && 'digit_database'[RWA_TYPE] = "X"
           )
        )
        VAR Ratio = 24 / Denominator
        RETURN SUMX(
           ADDCOLUMNS(
               digit_database,
               "CalculatedValue",
               IF(
                   [NWU] = "B" && [RWA_TYPE] = "X",
                   [RWA Digit Actual] * Ratio,
                   [RWA Digit Actual]
               )
           ),
           [CalculatedValue]
        )
        Result:

         

        Best regards,

         

        Joyce

         

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