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 think I'm able to do that part.

What I cannot figure out is adjusting the parent node and the total.

Any help would be highly appreciated.

 

 

 

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.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Petr_M,

    For your requirements, please create a new calculated column as shown below:

     

    Result =

VAR XSub =

CALCULATE(

    SUM('Table'[Sales]),

    FILTER(

        'Table',

        'Table'[SubCategory] = "X"

    )

)

VAR Divb = 24/XSub

VAR TotalSales =

IF(

    'Table'[SubCategory]= "X",

    'Table'[Sales] * Divb,

    'Table'[Sales]

)

RETURN

TotalSales

     

    Result:

    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 Anonymous, this looks promising!

       

      I only added the Category into identification of the node (as X may occur in other Categories as well) and it works perfectly.

       

      However, my real life scenario works with a measure instead of Sales. I should have realized this was relevant to my question. I attempted to apply the proposed logic there but my calculated column returns nothing. So I assume it needs to be a measure as well which likely changes the context and would require a more complex solution :/.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Petr_M ,
        Thank you for your reply and if possible, please upload your pbix example file so we can better test it for you.