Forum Discussion

dashbuilder's avatar
dashbuilder
New Member
5 years ago
Solved

Measure is calculated incorrectly in a table

Hi all,

I would appreciate it if someone could help me please with my measures:

I have the following table:

DateItemPrice
01.01.2020A20
01.01.2020A25
01.01.2020A30
03.01.2020A20
10.01.2020A35
02.01.2020B50
05.01.2020B60
01.01.2020C40
01.01.2020C45
01.01.2020C50
03.01.2020C40
10.01.2020C45


I have calculated the percentage change for each item taking the average price for the day based on the date slicer as follows:

 

 

 

Percent Change =
var _max = maxx(Table,Table[Date].[Date])
var _min = MINX(Table,Table[Date].[Date])
return
(CALCULATE(AVERAGE(Table[Price]), FILTER(ALLSELECTED(Table),Table[Date].[Date]=_max))-CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED(Table),Table[Date].[Date]=_min)))/CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED(Price),Table[Date].[Date]=_min))*100

 

 

 

 

 When I select individual items, the percentage is calculated correctly between min and max dates as follows:

But when I select all items, I get different percentage changes

Can anyone please help me to get the correct percentage changes when I select all items (the same values as when I select just one item)? I would like to present changes for all items in a table.

 

Thanks

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    dashbuilder , Create a separate date table and then try same formula with date table 

    example - 

    Percent Change =
    var _max = maxx('DATE','DATE'[Date])
    var _min = MINX('DATE','DATE'[Date])
    return
    (CALCULATE(AVERAGE(Table[Price]), FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_max))
    -CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_min)))/
    CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_min))*100

     

    Also, use the divide function 

    • dashbuilder's avatar
      dashbuilder
      New Member
      5 years ago

      amitchandak thank you for your response. I have created a new data table as follows

      Then, I altered the formula as suggested:

      Percent Growth = 
var _max = maxx('DATE','DATE'[Date])
var _min = MINX('DATE','DATE'[Date])
return
DIVIDE((CALCULATE(AVERAGE(Table[Price]), FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_max))
-CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_min))),
CALCULATE(AVERAGE(Table[Price]),FILTER(ALLSELECTED('DATE'),'DATE'[Date]=_min)))*100

      Now, I get "0"s:

      Am I doing something wrong? Thank you heaps.