Forum Discussion

thomazinh's avatar
thomazinh
Helper I
4 years ago
Solved

What If parameter matrix subtotal mismatch

Hierarchy values in my matrix are not returning the correct subtotal values when calling out a What If parameter. Here is a link to my sample PBI file

 

For example, for Arch AAA should total to 1.5, not 6. The What If parameters affect the _Unfactored What If and _Selected Bid columns. Both these columns are calculated. Below is the DAX I tried to yield to get the correct subtotals based on some documentation I found, but I was unsucessful. 

 

EDIT - In my actual file, the Project Name (Arch and Bould) are in a seperate related table by Project ID. Is there anyway that this rollup of hierarchy can still work?

 

 

_Selected Bid = 
// Displays the Bid Factor for the selected Bid in the What If Parameters 
VAR ShowSelected = 
SWITCH( TRUE(),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "AAA"), SELECTEDVALUE(Parameter_AAA[Parameter_AAA]),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "BBB"), SELECTEDVALUE(Parameter_BBB[Parameter_BBB]),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "FFF"), SELECTEDVALUE(Parameter_FFF[Parameter_FFF]),
SUM(Table4[Bid Value]))

VAR Result = 
SWITCH (
    TRUE (),
    ISFILTERED( Table4[WbsCode] ), ShowSelected,
    ISFILTERED ( Table4[Trade] ), ShowSelected, 
    ISFILTERED ( Table4[Project Name] ), ShowSelected, BLANK()
)
RETURN
    Result 

 

 

 

_Unfactored What If = 
// Returns the Unfactored Hours with the chosen Parameters from the What If sliders
VAR AAABid =
    SELECTEDVALUE ( Parameter_AAA[Parameter_AAA] ) //Grabbing the selected what if parameter for AAA
VAR BBBBid =
    SELECTEDVALUE ( Parameter_BBB[Parameter_BBB] ) //Grabbing the selected what if parameter for BBB
VAR FFFBid =
    SELECTEDVALUE ( Parameter_FFF[Parameter_FFF] ) //Grabbing the selected what if parameter for FFF
VAR Hours =
    SUM ( Table4[Hours] ) //Sum of Hours
VAR Unfactored =
    DIVIDE ( Hours, SUM ( Table4[Bid Value] ), 0 ) // Unfactored Desired value
// Factored valued returns the selected Parameter for the WbsCode containing that string. For example, Parameter_AAA should only influence WbsCodes with that Trade type.
VAR Result =
SWITCH( TRUE(),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "AAA"), DIVIDE( Hours, AAABid, 0),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "BBB"), DIVIDE( Hours, BBBBid, 0),
    CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "FFF"), DIVIDE( Hours, FFFBid, 0),
    Unfactored
)

RETURN
    Result 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi thomazinh ,

     

    Modify _Selected Bid as below.

    _Selected Bid = 
    VAR ShowSelected = 
    SWITCH(TRUE(),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "AAA"), SELECTEDVALUE(Parameter_AAA[Parameter_AAA]),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "BBB"), SELECTEDVALUE(Parameter_BBB[Parameter_BBB]),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "FFF"), SELECTEDVALUE(Parameter_FFF[Parameter_FFF]),
        SELECTEDVALUE(Table4[Bid Value])
        )
    RETURN
    if(ISINSCOPE(Table4[WbsCode]),ShowSelected,0)

     Then create a new measure.

    Measure = SUMX(Table4,[_Selected Bid])

     

    Best Regards,

    Jay

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi thomazinh ,

     

    Modify _Selected Bid as below.

    _Selected Bid = 
    VAR ShowSelected = 
    SWITCH(TRUE(),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "AAA"), SELECTEDVALUE(Parameter_AAA[Parameter_AAA]),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "BBB"), SELECTEDVALUE(Parameter_BBB[Parameter_BBB]),
        CONTAINSSTRING( SELECTEDVALUE(Table4[WbsCode]), "FFF"), SELECTEDVALUE(Parameter_FFF[Parameter_FFF]),
        SELECTEDVALUE(Table4[Bid Value])
        )
    RETURN
    if(ISINSCOPE(Table4[WbsCode]),ShowSelected,0)

     Then create a new measure.

    Measure = SUMX(Table4,[_Selected Bid])

     

    Best Regards,

    Jay