Calculated column or Measure

[kumarncenterp]

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
31/09/2019	NA	CP	RE LLC	-5538.88	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	27503662.9	0	0	RV not RB
31/09/2019	NA	CP	SDL	649975.71	0	0	RV not RB
31/09/2019	NA	CP	LLC	0	63250000	0	RB not RV
31/09/2019	NA	CP	RE LLC	-25164.39	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	58994293.83	0	0	RV not RB
31/09/2019	NA	CP	Sr Direct	5186435.28	0	0	RV not RB
31/09/2019	NA	CP	MASTER	1547601.64	0	0	RV not RB
31/09/2019	NA	CP	LLC	-286817.1	0	0	RV not RB
31/09/2019	NA	CP		-390881573.7	0	-390881573.7	RV not RB
31/09/2019	NA	CP	MASTER	568971.21	0	0	RV not RB
31/09/2019	NA	CP	SDL	974963.57	0	0	RV not RB
31/09/2019	NA	CP	MASTER	7582664.36	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	-21568.46	0	0	RV not RB
31/09/2019	NA	CP	MASTER	0	79137505.31	0	RB not RV
31/09/2019	NA	CP	RE LLC	0	390878641	0	RB not RV
31/09/2019	NA	CP	MASTER	29265193.76	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	77733758.95	0	0	RV not RB
31/09/2019	NA	CP	Sr Direct	0	22498226.05	0	RB not RV
31/09/2019	NA	CP	Sr Direct	2956147.29	0	0	RV not RB
31/09/2019	NA	CP	LLP	88485907.97	0	0	RV not RB
31/09/2019	NA	CP	SDL	883966.93	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	15923.33	0	0	RV not RB
31/09/2019	NA	CP	CREEK	-149984.88	0	0	RV not RB
31/09/2019	NA	CP	MASTER	10312423.5	0	0	RV not RB
31/09/2019	NA	CP	Sr Direct	1343703.21	0	0	RV not RB
31/09/2019	NA	CP	MASTER	16681861.71	0	0	RV not RB
31/09/2019	NA	CP		539960856.4	0	539960856.4	RV not RB
31/09/2019	NA	CP	LLC	63250000	0	0	RV not RB
31/09/2019	NA	CP	Sr Direct	1827436.51	0	0	RV not RB
31/09/2019	NA	CP	MASTER	7582664.36	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	-20067.4	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	-12618.43	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	13971909.66	0	0	RV not RB
31/09/2019	NA	CP	LLC	-7933.97	0	0	RV not RB
31/09/2019	NA	CP	MASTER	-244321.66	0	0	RV not RB
31/09/2019	NA	CP	Direct	671851.81	0	0	RV not RB
31/09/2019	NA	CP	SDL	324987.86	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	-88633.25	0	0	RV not RB
31/09/2019	NA	CP	MASTER	2503473.21	0	0	RV not RB
31/09/2019	NA	CP	SDL	649975.71	0	0	RV not RB
31/09/2019	NA	CP	MASTER	1706913.64	0	0	RV not RB
31/09/2019	NA	CP	MASTER	1137942.43	0	0	RV not RB
31/09/2019	NA	CP	LLP	0	77141559.38	0	RB not RV
31/09/2019	NA	CP		-0.01	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	102628612.5	0	0	RV not RB
31/09/2019	NA	CP	Sr Direct	1343703.21	0	0	RV not RB
31/09/2019	NA	CP	MASTER	0	13686282.32	0	RB not RV
31/09/2019	NA	CP	Sr Direct	0	3276889.098	0	RB not RV
31/09/2019	NA	CP	MASTER	3791332.07	0	0	RV not RB
31/09/2019	NA	CP	SDL	1429946.36	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	67960383.43	0	0	RV not RB
31/09/2019	NA	CP	RE LLC	42086019.53	0	0	RV not RB

Table 02:

Table result using Select group by query in sql:
DATE	USER	DESC	NAME	RV	RB	Diff	FLAG
31/09/2019	NA	CPL	Sr Direct	12657425.5	25775115.15	0	RV and RB
31/09/2019	NA	CPL	LLP	88485907.97	77141559.38	0	RV and RB
31/09/2019	NA	CPL	LLC	62955248.93	63250000	0	RV and RB
31/09/2019	NA	CPL	RE LLC	390720973.3	390878641	0	RV and RB
31/09/2019	NA	CPL	MASTER	74971818.1	79137505.31	0	RV and RB
31/09/2019	NA	CPL	PARALLEL	8602844.56	13686282.32	0	RV and RB
31/09/2019	NA	CPL	Direct	671851.81	0	0	RV not RB
31/09/2019	NA	CPL		149079282.7	0	149079282.7	RV not RB
31/09/2019	NA	CPL	SDL	4913816.14	0	0	RV not RB
31/09/2019	NA	CPL	CREEK	-149984.88	0	0	RV not RB

Now I have to create a Flag like

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

I need the Flag result similar to the second table i.e. group by table. Due to the granular level data the flag condition is calculating at each row level and not giving the expected result as table-02.

Please help me on how a calculated column or Measure can be written to get the Flag result similar to table-02


Thanks!

[v-jayw-msft · Accepted solution]

Hi @kumarncenterp ,

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

[Krutigawale33]

Hello @kumarncenterp ,

its working with the similar measure you created. If we use table visual and drop all fields to it its showing aggregated data.

let me know if I misunderstood the scenario

[jdbuchanan71]

Ah, I did misunderstand.  You will need 4 measures.

RV Amount = SUM ( 'Table'[RV] )

RB Amount = SUM ( 'Table'[RB] )

Diff Amount = SUM ( 'Table'[Diff] )

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

I think that will give what you are looking for.

[jdbuchanan71]

@kumarncenterp 

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.

[kumarncenterp]

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!

[kumarncenterp]

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!

[Krutigawale33]

Hello @kumarncenterp ,

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"))