Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kumarncenterp
Frequent Visitor

Calculated column or Measure

Hi,

 

Can anyone please help me on the below scenario.

 

Table 01: 

Data in Power BI:
DATEUSERDESCNAMERVRBDiffFLAG
31/09/2019NACPMASTER1137942.4300RV not RB
31/09/2019NACPRE LLC-5538.8800RV not RB
31/09/2019NACPRE LLC27503662.900RV not RB
31/09/2019NACPSDL649975.7100RV not RB
31/09/2019NACPLLC0632500000RB not RV
31/09/2019NACPRE LLC-25164.3900RV not RB
31/09/2019NACPRE LLC58994293.8300RV not RB
31/09/2019NACPSr Direct5186435.2800RV not RB
31/09/2019NACPMASTER1547601.6400RV not RB
31/09/2019NACPLLC-286817.100RV not RB
31/09/2019NACP -390881573.70-390881573.7RV not RB
31/09/2019NACPMASTER568971.2100RV not RB
31/09/2019NACPSDL974963.5700RV not RB
31/09/2019NACPMASTER7582664.3600RV not RB
31/09/2019NACPRE LLC-21568.4600RV not RB
31/09/2019NACPMASTER079137505.310RB not RV
31/09/2019NACPRE LLC03908786410RB not RV
31/09/2019NACPMASTER29265193.7600RV not RB
31/09/2019NACPRE LLC77733758.9500RV not RB
31/09/2019NACPSr Direct022498226.050RB not RV
31/09/2019NACPSr Direct2956147.2900RV not RB
31/09/2019NACPLLP88485907.9700RV not RB
31/09/2019NACPSDL883966.9300RV not RB
31/09/2019NACPRE LLC15923.3300RV not RB
31/09/2019NACPCREEK-149984.8800RV not RB
31/09/2019NACPMASTER10312423.500RV not RB
31/09/2019NACPSr Direct1343703.2100RV not RB
31/09/2019NACPMASTER16681861.7100RV not RB
31/09/2019NACP 539960856.40539960856.4RV not RB
31/09/2019NACPLLC6325000000RV not RB
31/09/2019NACPSr Direct1827436.5100RV not RB
31/09/2019NACPMASTER7582664.3600RV not RB
31/09/2019NACPRE LLC-20067.400RV not RB
31/09/2019NACPRE LLC-12618.4300RV not RB
31/09/2019NACPRE LLC13971909.6600RV not RB
31/09/2019NACPLLC-7933.9700RV not RB
31/09/2019NACPMASTER-244321.6600RV not RB
31/09/2019NACPDirect671851.8100RV not RB
31/09/2019NACPSDL324987.8600RV not RB
31/09/2019NACPRE LLC-88633.2500RV not RB
31/09/2019NACPMASTER2503473.2100RV not RB
31/09/2019NACPSDL649975.7100RV not RB
31/09/2019NACPMASTER1706913.6400RV not RB
31/09/2019NACPMASTER1137942.4300RV not RB
31/09/2019NACPLLP077141559.380RB not RV
31/09/2019NACP -0.0100RV not RB
31/09/2019NACPRE LLC102628612.500RV not RB
31/09/2019NACPSr Direct1343703.2100RV not RB
31/09/2019NACPMASTER013686282.320RB not RV
31/09/2019NACPSr Direct03276889.0980RB not RV
31/09/2019NACPMASTER3791332.0700RV not RB
31/09/2019NACPSDL1429946.3600RV not RB
31/09/2019NACPRE LLC67960383.4300RV not RB
31/09/2019NACPRE LLC42086019.5300RV not RB

 

Table 02:

 

Table result using Select group by query in sql:
DATEUSERDESCNAMERVRBDiffFLAG
31/09/2019NACPLSr Direct12657425.525775115.150RV and RB
31/09/2019NACPLLLP88485907.9777141559.380RV and RB
31/09/2019NACPLLLC62955248.93632500000RV and RB
31/09/2019NACPLRE LLC390720973.33908786410RV and RB
31/09/2019NACPLMASTER74971818.179137505.310RV and RB
31/09/2019NACPLPARALLEL8602844.5613686282.320RV and RB
31/09/2019NACPLDirect671851.8100RV not RB
31/09/2019NACPL 149079282.70149079282.7RV not RB
31/09/2019NACPLSDL4913816.1400RV not RB
31/09/2019NACPLCREEK-149984.8800RV 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!
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

8.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

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

8.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Krutigawale33
Responsive Resident
Responsive Resident

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

 

Krutigawale33_0-1620799566068.png

 

jdbuchanan71
Super User
Super User

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_0-1620799184615.png

 

 

jdbuchanan71
Super User
Super User

@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.

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!

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!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.