Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
)
)
)
)
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |