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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply

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.

 

f4ddfd4a-cfcd-4d28-b37c-2f1f08a32246.png

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

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

vcgaomsft_0-1700789215950.png

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

View solution in original post

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

View solution in original post

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

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

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

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)

 

gsunilkumar145_0-1701237421151.png

 

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

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

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

 

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

vcgaomsft_0-1700789215950.png

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

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.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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