Forum Discussion

ktt777's avatar
ktt777
Helper V
5 years ago
Solved

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...
  • amitchandak's avatar
    5 years ago

    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's avatar
    5 years ago

    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