Hi, Can anyone please help me on the below scenario. Table 01: Data in Power BI:DATEUSERDESCNAMERVRBDiffFLAG31/09/2019NACPMASTER1137942.4300RV not RB31/09/2019NACPRE LLC-5538.8800RV not RB31/09/2019NACPRE LLC27503662.900RV not RB31/09/2019NACPSDL649975.7100RV not RB31/09/2019NACPLLC0632500000RB not RV31/09/2019NACPRE LLC-25164.3900RV not RB31/09/2019NACPRE LLC58994293.8300RV not RB31/09/2019NACPSr Direct5186435.2800RV not RB31/09/2019NACPMASTER1547601.6400RV not RB31/09/2019NACPLLC-286817.100RV not RB31/09/2019NACP -390881573.70-390881573.7RV not RB31/09/2019NACPMASTER568971.2100RV not RB31/09/2019NACPSDL974963.5700RV not RB31/09/2019NACPMASTER7582664.3600RV not RB31/09/2019NACPRE LLC-21568.4600RV not RB31/09/2019NACPMASTER079137505.310RB not RV31/09/2019NACPRE LLC03908786410RB not RV31/09/2019NACPMASTER29265193.7600RV not RB31/09/2019NACPRE LLC77733758.9500RV not RB31/09/2019NACPSr Direct022498226.050RB not RV31/09/2019NACPSr Direct2956147.2900RV not RB31/09/2019NACPLLP88485907.9700RV not RB31/09/2019NACPSDL883966.9300RV not RB31/09/2019NACPRE LLC15923.3300RV not RB31/09/2019NACPCREEK-149984.8800RV not RB31/09/2019NACPMASTER10312423.500RV not RB31/09/2019NACPSr Direct1343703.2100RV not RB31/09/2019NACPMASTER16681861.7100RV not RB31/09/2019NACP 539960856.40539960856.4RV not RB31/09/2019NACPLLC6325000000RV not RB31/09/2019NACPSr Direct1827436.5100RV not RB31/09/2019NACPMASTER7582664.3600RV not RB31/09/2019NACPRE LLC-20067.400RV not RB31/09/2019NACPRE LLC-12618.4300RV not RB31/09/2019NACPRE LLC13971909.6600RV not RB31/09/2019NACPLLC-7933.9700RV not RB31/09/2019NACPMASTER-244321.6600RV not RB31/09/2019NACPDirect671851.8100RV not RB31/09/2019NACPSDL324987.8600RV not RB31/09/2019NACPRE LLC-88633.2500RV not RB31/09/2019NACPMASTER2503473.2100RV not RB31/09/2019NACPSDL649975.7100RV not RB31/09/2019NACPMASTER1706913.6400RV not RB31/09/2019NACPMASTER1137942.4300RV not RB31/09/2019NACPLLP077141559.380RB not RV31/09/2019NACP -0.0100RV not RB31/09/2019NACPRE LLC102628612.500RV not RB31/09/2019NACPSr Direct1343703.2100RV not RB31/09/2019NACPMASTER013686282.320RB not RV31/09/2019NACPSr Direct03276889.0980RB not RV31/09/2019NACPMASTER3791332.0700RV not RB31/09/2019NACPSDL1429946.3600RV not RB31/09/2019NACPRE LLC67960383.4300RV not RB31/09/2019NACPRE LLC42086019.5300RV not RB Table 02: Table result using Select group by query in sql:DATEUSERDESCNAMERVRBDiffFLAG31/09/2019NACPLSr Direct12657425.525775115.150RV and RB31/09/2019NACPLLLP88485907.9777141559.380RV and RB31/09/2019NACPLLLC62955248.93632500000RV and RB31/09/2019NACPLRE LLC390720973.33908786410RV and RB31/09/2019NACPLMASTER74971818.179137505.310RV and RB31/09/2019NACPLPARALLEL8602844.5613686282.320RV and RB31/09/2019NACPLDirect671851.8100RV not RB31/09/2019NACPL 149079282.70149079282.7RV not RB31/09/2019NACPLSDL4913816.1400RV not RB31/09/2019NACPLCREEK-149984.8800RV not RB Now I have to create a Flag likeFlag = 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-02Thanks!

Forum Discussion

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
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!

Need Help

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

7 Replies

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
  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
    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!

jdbuchanan71

Super User

5 years ago

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.

Anonymous
Not applicable
5 years ago
Hello Anonymous ,
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
Anonymous
Not applicable
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

Forum Discussion

Calculated column or Measure

7 Replies

Related content

Measure or Calculated Column

TMDL - calculated column

DAX Calculated Measure

Convert calculated column into measure

Write logic for Calculated Column or Measure