Forum Discussion

ngocnguyen's avatar
ngocnguyen
Helper IV
4 years ago

Subtotal sum

Hi,

Hi

I have somes measure to get result of factors: A,B,C,D,Cost

Logic for  :" Cost (exclude factor) = Cost - remaining factors 
Now I wanna create a matrix that like below 

{ Cost ( exclude factor) = Cost - (A+B+C+D) = 104-(10+34+22+23) = 25}

 

in case If I remove some factors (A,B,C,D), the matrix will auto calculate " Cost (Exclude factor)". 
For example:

 


{ Cost ( exclude factor) = Cost - (A+C+D) = 104-(10+22+23) = 25}

Is there anyway to do it in PBI ?, by creating measure?

 

 

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please try to write a measure something like below.

     

    Expected measure: =
SWITCH (
    SELECTEDVALUE ( 'tablename'[Factor] ),
    "Cost(exclude factor)"
        = CALCULATE (
            SUM ( 'tablename'[Amt] ),
            FILTER ( ALLSELECTED ( 'tablename' ), 'tablename'[Factor] = "Cost" )
        )
            - CALCULATE (
                SUM ( 'tablename'[Amt] ),
                FILTER ( ALLSELECTED ( 'tablename' ), 'tablename'[Factor] <> "Cost" )
            ), SUM ( 'tablename'[Amt] )
)
    • ngocnguyen's avatar
      ngocnguyen
      Helper IV
      4 years ago

      Hi Jihwan_Kim 

      Thanks for  your help

      However, My issue is that, each reasult in column "Amt" in matrix are Measure
      For example:

      A= [A],  B = [B], C =[C], Cost =[Cost]
      So logic for Cost (exclude factor) = Cost - remaining factors 
      Then to issue the matrix I create a measure like:

      Amt = 

      SWITCH( TRUE(),
      SELECTEDVALUE(table[Factor])= "A",[A],
      SELECTEDVALUE(table[Factor])= "B",[B],
      SELECTEDVALUE(table[Factor])= "C",[C],
      SELECTEDVALUE(table[Factor])= "Cost",[Cost],
      Blank())

      So, with this measure I just can create a matrix as below 
       
      Now I wanna create a measure so that the Cost (Exclude factor ) = Cost - Factors appearing in matrix like this one: