Search code examples
ssaspowerbidaxtabular

Average of a Measure in DAX

i need a dax function to calculate the average of a measure.

There is a metric Shelf life which is difference of current date and first inward date. First inward date is the date when a particular material arrives to a store for the first time. The metric should me made like that when it is viewed in overall level it should give the average of individual shelf lives(store and material level).

I tried using the summarize function to group the table with material and store. then on top of that i have used averagex function to calculate the average. Also tried sumx function on top of it and divide it by distinct count of rows.

POWERBI output The image shows the output in power bi ( ideally shelf life and SL test1 should be same and should give 5.78 in total)

Power BI output:

enter image description here

MEASURES

First Inward Date:= MIN(Inward[Rdate])

Shelf life := DIVIDE(MAX('Date'[Date])-[First Inward Date],7,0)

SL test1 := CALCULATE(AVERAGEX(SUMMARIZE(Inward,Inward[StoreMaterial],"avg", DIVIDE(MAX('Date'[Date])-[First Inward Date],7,0) ),[avg]))

CUBE Structure This image shows the structure of inward table in SSAS tabular cube

Cube Structure:

enter image description here

Solution

  • You should also be able to simplify the calculation:

    SL test1 := 
    AVERAGEX(
      VALUES(Inward[StoreMaterial]),
      [Shelf life]
    )