topic Re: Median on the top of measure in DAX Commands and Tips

Median on the top of measure

PawelWitecki — Sat, 01 Oct 2022 08:25:38 GMT

Hi, I have the table with columns: country, week, day, status, order and measure with share of orders in each of statuses. I need to calculate 1) the share for every country/week, then a median of shares per country, and have it in dashboard for every country/week level; 2) the share for country, then a total median of those shares, and have it in dashboard for every country level. It will be used for conditional formating to compare for upper-level median: country/week share vs country median and country share vs total median.

I was able to calculate 1) using MEDIANX at the country/day level but I have separate tables for country and day. Is it possible to calculate it for country/week without creating week table?

How to calculate 2) total median from country level aggregation? MEDIANX won't help here as there is no iteration. And MEDIAN isn't working on measure.

Re: Median on the top of measure

tamerj1 — Sat, 01 Oct 2022 09:33:42 GMT

Hi PawelWitecki
Would you please include some screeschots, a sample data and the expected results for better understanding of the problem?

Re: Median on the top of measure

daXtreme — Sat, 01 Oct 2022 11:09:42 GMT

To caclulate over weeks you have to have a calendar with defined week, easy as that. To calculate median you have to have data points to calculate it from. What does it mean "total median from country level aggregation"? A median is the middle value of some observations. Therefore, where there are observations (it better be more then 1!), there is a median. The median of 1 observation is just this observation (not very useful, though). If you want to calculate a median from a measure, you have to be able to say what you're going to iterate the measure over. Without this, the operation loses any meaning.