Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Please see below data set,As per my reuirements if REGION DESC=”Europe” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600) and if REGION DESC=”ASIA” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600)…so on.
I have used if and switch but sum does not filter by region desc and class.
Can any one has any suggeston?
thanks
Region Desc | Class | Impact |
Europe | PART | 100 |
Europe | Hardware | 200 |
Europe | software | 300 |
Europe | SERVICE | 400 |
Asia | SERVICE | 500 |
Asia | PART | 600 |
Asia | Hardware | 700 |
Asia | software | 800 |
Solved! Go to Solution.
Hi,
Drag Region to the Table visual and Class to the filter section. In the Filter, select your desired classes. Write this measure
=SUM(Data[Value])
Hope this helps.
Hi,
Based on the data that you have shared, please share the exact result. For Asia the numbers are not 100,200 and 300. So i am confused. Also, do you want a measure or a calculated column formula?
Hi,
I am really sorry for typo.
For Region=asia,classs=service,part,software and sum of impact=500+600+800=1900.
From my dataset I can say calculated perform well.
Let me know if you have any suggestion.
Thanks
Hi,
So you want a calculated column formula. Share the exact result you are expecting in a spare column of the Table that you shared i your original post.
Hi Ashish,
Please see below,
My expected result,
Region | Total Impact |
Europe | 600 |
Asia | 1900 |
>Total Impact For Region=asia,classs=service,part,software and sum of impact=500+600+800=1900.
>Total Impact REGION DESC=”Europe” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600)
Please see my approch and suggest me any changes,with below steps I am getting right result but I need to created at least 50 column and after that I am going to use switch.Please suggested me any efficient way.
A.I have created tow columns,one for europe and one for asia.
First column:asia =
IF (
CALCULATE (
SUM ( TABLE_5[IMPACT] ),
FILTER (
TABLE_5,
[Region Desc] = "ASIA"
),
FILTER (
TABLE_5,
[Class] = "ACCESSORY"
|| [Class] = "CONSUMABLE"
|| [Class] = "SOFTWARE"
)
Second Column:Europe=delete for me 2 =
IF (
CALCULATE (
SUM ( TABLE_5[IMPACT] ),
FILTER (
TABLE_5,
[Region Desc] = "Europe"
),
FILTER (
TABLE_5,
[Class] = "ACCESSORY"
|| [Class] = "CONSUMABLE"
|| [Class] = "SOFTWARE"
)
B.I have used switch,
Total Impact=switch(true(),region desc=europe,europe,
Region desc=asia,asia),0)
Hi,
Drag Region to the Table visual and Class to the filter section. In the Filter, select your desired classes. Write this measure
=SUM(Data[Value])
Hope this helps.
@Anonymous
Create a conditional column from EditQueries option like below image.
Hi Chotu,
In conditional column "THEN"wont allow me to SUM(IMPACT) and region and desc is not correctly populated data.
thanks
Hello Eveyone,
I have used below formula but did not get correct result.
Is there any suggestion?
delete for me 2 =
IF (
CALCULATE (
SUM ( TABLE_5[IMPACT] ),
FILTER (
TABLE_5,
[Region Desc] = "ASIA"
),
FILTER (
TABLE_5,
[Class] = "ACCESSORY"
|| [Class] = "CONSUMABLE"
|| [Class] = "SOFTWARE"
)
),
(
CALCULATE (
SUM ( TABLE_5[IMPACT] ),
FILTER (
TABLE_5,
[Region Desc] = "EUROPE"
),
FILTER (
TABLE_5,
[Class] = "ACCESSORY"
|| [Class] = "CONSUMABLE"
|| [Class] = "SOFTWARE"
)
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |