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

Petr_M's avatar
Petr_M
Frequent Visitor
2 years ago

Thank you so much, works perfectly!