Variable calculation

[ktt777]

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, 

[amitchandak · Accepted solution]

@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"
)
)

[PhilipTreacy · Accepted solution]

Hi @ktt777 

Download sample PBIX file

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

[amitchandak · Accepted solution]

@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"
)
)