sum the above rows & subtract the other row

[gsunilkumar145]

Hi All,

i need help on the below query, Am having 5 columns in which 3 columns having value.

a=4, b=5, c=a+b , d=10, e=d-c

the above one i need to achive. find the attached screenshot for yourreference.

I have tried the below query for sum by creating new column but it showing empty results in the column C.

Column = SWITCH(data[col1],"a",data[value],

"b",data[value],

"c",CALCULATE(SUM(data[value]),data[col1] in {"a","b"}),

"d",data[value])

[v-cgao-msft · Accepted solution]

Hi @gsunilkumar145 ，

Please try:

Column = 
VAR _a = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="a"))
VAR _b = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="b"))
VAR _c = _a + _b
VAR _d = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="d"))
VAR _e = _d - _c
VAR _result = 
SWITCH(
    'Table'[col1],
    "a",_a,
    "b",_b,
    "c",_c,
    "d",_d,
    "e",_e
)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Syndicate_Admin]

second request: Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[gsunilkumar145 · Accepted solution]

I found the below solution, i have used the calculate insted of switch & added the slicers in allexcept .

VAR sales_revenue =

    CALCULATE(SUM('public fin_pl_report_data'[value])+SUM('public fin_pl_report_data'[value])*MAX('public fin_pl_report_data'[Final_%Adjustment_Value])/100, 'public fin_pl_report_data'[gl_2]="Sales Revenue",ALLEXCEPT('public fin_pl_report_data','public fin_pl_report_data'[plant],'public fin_pl_report_data'[type],'public fin_pl_report_data'[month]))

VAR Feedstock_Cost =

    CALCULATE(SUM('public fin_pl_report_data'[value])+SUM('public fin_pl_report_data'[value])*MAX('public fin_pl_report_data'[Final_%Adjustment_Value])/100, 'public fin_pl_report_data'[gl_2]="Feedstock cost",ALLEXCEPT('public fin_pl_report_data','public fin_pl_report_data'[plant],'public fin_pl_report_data'[type],'public fin_pl_report_data'[month]))    

VAR P2F = sales_revenue + Feedstock_Cost

[gsunilkumar145]

The above table is summarisation of  values, so i cant use max value & its having slicers.

I tried using the below query but am getting blanks /nulls values where ever variable sum & subtration (ex: P2F). 

trail1: 

Column 2 =
var sales_revenue=SWITCH('public fin_pl_report_data'[gl_2],"sales revenue",'public fin_pl_report_data'[value],ALLEXCEPT('public fin_pl_report_data','public fin_pl_report_data'[plant],'public fin_pl_report_data'[type],'public fin_pl_report_data'[month]))
var Feedstock_Cost=SWITCH('public fin_pl_report_data'[gl_2],"Feedstock cost",'public fin_pl_report_data'[value],ALLEXCEPT('public fin_pl_report_data','public fin_pl_report_data'[plant],'public fin_pl_report_data'[type],'public fin_pl_report_data'[month]))
var P2F_1=sales_revenue+Feedstock_Cost

var results=
SWITCH('public fin_pl_report_data'[gl_2],
"Sales Revenue",sales_revenue,
"Feedstock cost",Feedstock_Cost,
"P2F",P2F_1)
return
results
Error: the expression referes to multiple column, multiple columns cant converted to a scalar values  .
 
Trail2: 
Column 2 =
var sales_revenue=SWITCH('public fin_pl_report_data'[gl_2],"sales revenue",'public fin_pl_report_data'[value])
var Feedstock_Cost=SWITCH('public fin_pl_report_data'[gl_2],"Feedstock cost",'public fin_pl_report_data'[value])
var P2F_1=sales_revenue+Feedstock_Cost

var results=
SWITCH('public fin_pl_report_data'[gl_2],
"Sales Revenue",sales_revenue,
"Feedstock cost",Feedstock_Cost,
"P2F",P2F_1)
return
results
output: variable (P2F_1) of sum & subtration is displaying null.

[Syndicate_Admin]

Power BI has no idea what you are mean by "above row"  and "other row".  You need to bring your own index.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[gsunilkumar145]

Thank you , I understand power BI cant go with above & other rows, Please find the image in trail query.

col1 is my reference , while creating new column based on col1,

if col1 is "a" then value i.e 4.,

if col1 is "b" then value i.e 5.,

if col1 is "c" then value should be a+b,

if col1 is "e" then value should be d-c, this my requirement .

addtion a+b am able to achieve it by removing the context filter (expect) but i dont know how to write the subtraction , how modify or write the below query for e

SWITCH(data[col1],"a",data[value],

"b",data[value],

"c",CALCULATE(SUM(data[value]),data[col1] in {"a","b"},ALLEXCEPT(data,data[col1])),

"d",data[value])

[v-cgao-msft · Accepted solution]

Hi @gsunilkumar145 ，

Please try:

Column = 
VAR _a = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="a"))
VAR _b = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="b"))
VAR _c = _a + _b
VAR _d = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[col1]="d"))
VAR _e = _d - _c
VAR _result = 
SWITCH(
    'Table'[col1],
    "a",_a,
    "b",_b,
    "c",_c,
    "d",_d,
    "e",_e
)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[gsunilkumar145]

I cant use max , because hence its a dummy data single column we can use in above. In actual case am having the multiple rows of data along with multiple filter columns (sclicers), I tried below function but in varibale sum or subtract functions are not working.

Column 2 =

var sales_revenue=SWITCH('public fin_pl_report_data'[gl_2],"Sales Revenue",'public fin_pl_report_data'[value])

var Feedstock_Cost=SWITCH('public fin_pl_report_data'[gl_2],"Feedstock cost",'public fin_pl_report_data'[value])

var P2F_1=sales_revenue+Feedstock_Cost

var results=

SWITCH('public fin_pl_report_data'[gl_2],

"Sales Revenue",sales_revenue,

"Feedstock cost",Feedstock_Cost,

"P2F",P2F_1)

return

results

above query am getting the output but not getting for P2F.