Forum Discussion
Variable calculation
hi,
Here is my raw data:
| Fruit | Month | Sale |
| Apple | January | 9 |
| Orange | January | 16 |
| Apple | February | 22 |
| Orange | February | 45 |
| Apple | March | 23 |
| Orange | March | 16 |
I want to calculate the total sale based on the target as below :
| Fruit | Low | Medium | Max |
| Apple | 0-10 | 10-20 | more than 20 |
| Orange | 0-15 | 15-25 | More than 25 |
the final result should show the total sale within each target:
| Fruit | Low | Medium | Max |
| Apple | 9 | 0 | 45 |
| Orange | 0 | 32 | 45 |
How can i make it dynamtic in dax fomular so i can change the target and add more fruit type
thanks,
ktt777 , Create a new column like the below in you table and use in as column in Matrix
If([Fruit] ="Apple",
Switch ( True(),
[Sale] <=10 , "Low",
[Sales] <=20, "Medium",
"Max"
),
Switch ( True(),
[Sale] <=15 , "Low",
[Sales] <=25, "Medium",
"Max"
)
)Hi ktt777
Create 3 measures like so
Low = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] < 10 ))Medium = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] >= 10 && 'Table1'[Sale] < 20 ))Max = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] >= 20 ))Add these to a Table visual
If you add more fruit the measures will work for them
Regards
Phil
2 Replies
ktt777 , Create a new column like the below in you table and use in as column in Matrix
If([Fruit] ="Apple",
Switch ( True(),
[Sale] <=10 , "Low",
[Sales] <=20, "Medium",
"Max"
),
Switch ( True(),
[Sale] <=15 , "Low",
[Sales] <=25, "Medium",
"Max"
)
)Hi ktt777
Create 3 measures like so
Low = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] < 10 ))Medium = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] >= 10 && 'Table1'[Sale] < 20 ))Max = CALCULATE(SUM('Table1'[Sale]), FILTER('Table1', 'Table1'[Fruit] = SELECTEDVALUE('Table1'[Fruit]) && 'Table1'[Sale] >= 20 ))Add these to a Table visual
If you add more fruit the measures will work for them
Regards
Phil