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,

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.

    • Petr_M's avatar
      Petr_M
      Frequent Visitor
      2 years ago

      Sorry for the delay in getting back to you! I had some busy days and had to strip my pbix of useless and confidential data.

       

      On the right, there is your working example.

       

      On the left, I'm attempting to replicate it in a more complex scenario. I have a digit_database fact table with three months. I choose the Actual month and want to display the original data (Act) and the Result which has the value of B/X node and its children normalized to given B/X sum.
      I have different hierarchy level names (UNIT, RWA_TYPE, Segment1) and the hierarchy is defined outside of the fact table. So far, I was unable to figure this out.

       

       

      Example.pbix 

      • 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.