Power BI calculates Median incorrectly when the visual is showing items with no data

[sylvans]

As per subject, Power BI calculates Median (and the Median Line) incorrectly when the visual is showing items with no data.

In a typical Line Chart, when adding a Median Line (under Analytic tab) based on the values that you previously dragged under Field tab, Power BI will automatically calculate and generate a Median Line to your Line visual.

This Median Line (and its Data Label) is able to update dynamically if you choose Date Hierarchy as your X-axis. However, if you tick the options to show items with no data (e.g. a Line chart showing the number of pizzas ordered in a Chinese Takeaway across the past 5 years), you would have many months with 0 order, but Power BI is still ranking the orders without taking consideration towards those 0-order months.

Is there a way to workaround this? If it's a defect, could Microsoft fix this? Thanks a bunch!

[Anonymous · Accepted solution]

Hi, @sylvans 

Thanks @lbendlin.  Based on Sylvans' description, I created the following sample data:

I tried creating a Line chart visual and using the analyses feature of the chart to add the median curve as shown in the image below:

That's when I found out that I got the wrong median, and as @sylvans  described in the case, the curve would take into account dates for which there was no data.

Here's an alternative workaround to get the correct median by excluding dates for which there is no data:

I used the following DAX expression:

The median is subtracted from the null =
VAR _b =
    MEDIANX (
        SUMMARIZE (
            FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Pizza Orders] <> BLANK () ),
            'Table (2)'[Month-Year],
            'Table (2)'[Pizza Orders]
        ),
        'Table (2)'[Pizza Orders]
    )
RETURN
    _b

Here are the results:

In the sample data I used, the median = （50+60）/2=55. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Anonymous · Accepted solution]

Hi, @sylvans 

Thanks @lbendlin.  Based on Sylvans' description, I created the following sample data:

I tried creating a Line chart visual and using the analyses feature of the chart to add the median curve as shown in the image below:

That's when I found out that I got the wrong median, and as @sylvans  described in the case, the curve would take into account dates for which there was no data.

Here's an alternative workaround to get the correct median by excluding dates for which there is no data:

I used the following DAX expression:

The median is subtracted from the null =
VAR _b =
    MEDIANX (
        SUMMARIZE (
            FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Pizza Orders] <> BLANK () ),
            'Table (2)'[Month-Year],
            'Table (2)'[Pizza Orders]
        ),
        'Table (2)'[Pizza Orders]
    )
RETURN
    _b

Here are the results:

In the sample data I used, the median = （50+60）/2=55. I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[sylvans]

Hi both,

Thanks for the supports. Interesting enough, your proposed solution is the complete opposite of my question here! What I meant was the null values failed to be included in the calculation of PBI's default Median Line as expected.

As seen in below screen cap, I've simulated a dummy dataset. With 6 calendar quarters and 4 order types (A, B, C & one being null), the Median Line is expected to be based on these 24 variables, including values being 0 (i.e. quarters where there are no such order type).

This means the Median should've been 2.0 (i.e. the average of rank 12th & 13th values). But Power BI generates this Median value as 3, which seems to be ignoring the 0 value. I wonder if our different results are due to different Power BI Desktop version? (Mine is May2022)

Below is the a copy of the dummy dataset. Thanks again! P.S. If I activate the option "Show items with no data", the same unexpected exclusion takes place (which may appear counter-intuitive in hindsight, but it's actually making sense because if Date Hierarchy is applied, the source data should always be starting and ending on 1st Jan yyyy and 31st Dec yyyy respectively)

Order DateOrder Type ID

2021-11-04	C
2021-09-21	C
2021-12-01	C
2021-10-27	C
2021-12-19	C
2021-11-13	C
2022-03-03	C
2022-01-15	C
2021-10-12
2022-03-31	A
2022-03-25	A
2022-09-29	A
2022-07-21
2022-08-18
2022-06-26
2022-08-31
2022-01-29	B
2022-10-06	C
2022-04-24	B
2022-11-19	A
2022-09-22	A
2022-12-27	A
2022-08-13	C
2022-07-20	C
2021-10-31	A
2022-05-08
2021-12-15	A
2022-09-24	B
2021-11-18	A
2022-01-01	A
2022-09-09	A
2022-09-05	C
2021-12-02	C
2022-07-30	A
2022-10-13	A
2022-11-15	C
2022-09-24	C
2021-11-12	C
2021-12-07	C
2022-06-12
2022-10-06	C
2022-07-27	A
2022-08-29	A
2022-08-01	B
2022-07-07	B
2022-07-22	B
2022-05-05
2022-10-11	A
2022-08-17	B
2022-06-21	B
2021-10-24
2022-11-13	B

[sylvans]

Hi @Anonymous, 

a quick update: I think the root cause of my issue is Values field from my dataset is multi-types (A, B, C or null) where yours is just count of orders. When I change my dataset to count of index like yours, the Median Line is able to calculate dynamically and correctly; even when "Show items with no data" is activated for Axis field.

Having said that, shouldn't Power BI's bulit-in Medine Line be able to rank correctly for multi-type values too? Thank you!

FYR:

[Anonymous · Accepted solution]

Hi, @sylvans 

In the new version of Power BI, it seems that the average value is calculated correctly, as shown in the following image:

I have provided the PBIX file used this time below, I hope it can help you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[sylvans]

Hi @Anonymous ,

As I was saying the root cause of my issue is Values field from my dataset is multi-types (A, B, C or null). My older version of PBI Desktop is able to update the Median Line after activating "Show data with no items" (for x-axis) as well.

But I guess what you meant was the newer version of PBI Desktop would be able to cater for multiple types of values (i.e. multiple lines) as well. And thanks a lot for the DAX on excluding null values. It'll come in handy 😉

[lbendlin]

Median is just counting the items, it doesn't care of some other column has values or not.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...