Median AND sum by

[Kpham]

Dear all,

I would like to create a measure that's shows the median of the cost per year by product. Underneath a screenshot of my PBIX. In my original PBIX it don't have the SUM and column 6 column

I got my wrong result with the following DAX. It misses the first step to sum the cost by project.

~ Median 2018 (€) = CALCULATE(MEDIAN(Sheet1[Cost €]),YEAR(Sheet1[Year]=2018))

 pbix --> https://we.tl/t-nxcAYxsZZa

[v-alq-msft · Accepted solution]

Hi, @Kpham 

Based on your description, you may create a calculated column and two measures as below. The pbix file is attached in the end.

Calculated column:

Y = YEAR([Year])

Measure:

median 2018 = 
var tab = 
SUMMARIZE(
    Sheet1,
    Sheet1[Product],
    Sheet1[Project],
    Sheet1[Y],
    "Re",
    SUM(Sheet1[SUM])
)
return
MEDIANX(
    FILTER(
        tab,
        [Y]=2018
    ),
    [Re]
)

median 2019 = 
var tab = 
SUMMARIZE(
    Sheet1,
    Sheet1[Product],
    Sheet1[Project],
    Sheet1[Y],
    "Re",
    SUM(Sheet1[SUM])
)
return
MEDIANX(
    FILTER(
        tab,
        [Y]=2019
    ),
    [Re]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[darentengmfs]

@Kpham 

You can sum your customer by year using Group By in Power Query.

Then to calculate median, you can use the median function.

https://docs.microsoft.com/en-us/dax/median-function-dax

[Kpham]

Thanks but I didn't work.  I have uploaded the pbix to the post.

[darentengmfs]

@Kpham 

Please try using Group By on Product, Project, and Year, with the result SUM of Cost.