Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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],
Solved! Go to 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
I found the below solution, i have used the calculate insted of switch & added the slicers in allexcept .
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 .
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.
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
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
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.
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
44 | |
26 | |
21 | |
16 | |
12 |