Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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
Thanks but I didn't work. I have uploaded the pbix to the post.
User | Count |
---|---|
84 | |
84 | |
68 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |