cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## sum the above rows & subtract the other row

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])
2 ACCEPTED SOLUTIONS
Community Support

Hi @gsunilkumar145 ，

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

Helper I

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

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.

Helper I

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
Helper I

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.

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.

Helper I

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

Community Support

Hi @gsunilkumar145 ，

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

Helper I

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors