Perform row calculation in Power BI

[Anonymous]

Hi, I have something like this. I have a 7 columns under which one in index called "Index" and the rest 6 columns from "SRG" to "SFO".  I have 4 rows under "Index" called A,B, C and D. I want to add 4th row called as "D" highlightened in Green as seen where it calculates C/A (C divided by A) but only for columns SRG, PTS and ACSP and C + A(C added to A) for colums SNPT, ARG% and SFO. The question is how do I create a row "D" under "Index" Column in Power BI. 

@mwegener @az38 @parry2k 

[amitchandak]

You want to append a new row in table or the resultset ?

[Anonymous]

Hi Amit,

I want to append a new role in the table but the challenge is the columns like SRG, SFO etc are all measures as well.

So, is there a way where I can create row based on row by row caluclation for a measure. 

Appreciate your help.

[az38]

@Anonymous 

ifI understand you correct, you can create a calculated column 

CellFormat = IF([Index]="D", "Green", "Black")

the use a conditional formatting option which great described here  https://docs.microsoft.com/en-Us/power-bi/desktop-conditional-table-formatting#color-by-color-values  (Use Color by color values option)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 , yes that should be fine with respect to color prespective but wanted to know how to compute row D under Index Column where it computes rows C / A for the first 3 columns and C + A for the next 3 columns.

Appreciate your help!

[az38]

@Anonymous 

it depends on your data model, but as first suggestion you shoulduse the new ACSP column, for example, which is

New ASCP = 
var _ASCP_C = LOOKUPVALUE([ASCP], [Index], "C)
var _ASCP_A = LOOKUPVALUE([ASCP], [Index], "A)

RETURN
iF([Index]="D", DIVIDE(_ASCP_C,_ASCP_A), [ASCP])

do not hesitate to give a kudo to useful posts and mark solutions as solution