Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated column or Measure

Hi,   Can anyone please help me on the below scenario.   Table 01:  Data in Power BI: DATE USER DESC NAME RV RB Diff FLAG 31/09/2019 NA CP MASTER 1137942.43 0 0 RV not RB ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    You don't have a column to use as hierarchy so I doubt you could create a matrix table.

    Here's the formula for table visual.

    Measure = 
var rb = SUM('Table'[RB])
var rv = SUM('Table'[RV])
return
SWITCH(TRUE(),
        rb<>0&&rv<>0,"RB and RV",
        rb<>0&&rv=0,"RB not RV",
        rb=0&&rv<>0,"RV not RB"
              )

     

    Best Regards,

    Jay

jdbuchanan71's avatar
jdbuchanan71
Super User
5 years ago

Anonymous 

This should give you what you are looking for unless I misunderstand.

Flag = 
SWITCH (
    TRUE(),
    'Table'[RV] <> 0 && 'Table'[RB] <> 0, "RV and RB",
    'Table'[RV] <> 0 && 'Table'[RB] = 0, "RV not RB",
    'Table'[RV] = 0 && 'Table'[RB] <> 0, "RB not RV",
    "Not RV or RB"
)

 It would be a calculated column you add to the first table.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello,

Thanks for your help. But the switch condition is similar to my if statemnt and it is computing the flag column result at each row level, i want to compute at aggregated value level like Table -02 results

 

Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I forgot to mention that, i need to use the matrix visual for the reporting to showing hte details in hierarchy and drill down. But the matrix is not allowing to keep a measure field on the rows, the flag should be my starting point and upon drill down within that flag buckets i will show the other columns.
    Thats the reason i was trying to do using calculated column but no luck

     

    Thanks much!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello Anonymous ,

       

      Still I am not sure how would you like to show it in matrix,

       

      but if your need is to create column for flag with aggregated result then you can try below,

       

      Create a New table,

      Table = DISTINCT(Table01[NAME])
       
      create a relationship between this two table based on Name column
      Add new columns in this table,
       
      RV_agg = CALCULATE(SUM(Table01[RV]),GROUPBY(Table01,Table01[NAME]))
      RB_agg = CALCULATE(SUM(Table01[RB]),GROUPBY(Table01,Table01[NAME]))

      Flag = IF('Table'[RV_agg]<>0 && 'Table'[RB_agg]<>0,"RV and RB",IF('Table'[RV_agg]<>0 && 'Table'[RB_agg]=0,"RV not RB","RB not RV"))