median of metric per category when metric > 0

[Michella]

Hi everyone,

I need help, please.

I have two tables in a datamodel connected via sailingKey which is a unique identifier. They have crossfiltering enabled, as seen here:

 

In the data I have different itineraries (e.g. from A-B and C-D, ...). Each itinerary consists of multiple sailing keys.

I want to calculate the median of the metric for each itinerary but only for those sailing keys that have a value greater than 0 in the metric. The metric comes from the shipreadings table, everything else from dimsailing. 

I have a partially working formula which changes based on what I filter but I do not want the number to change:

Median per Itinerary = 

IF(ISBLANK([Metric]), BLANK(),
    DIVIDE(
        MEDIANX (
            SUMMARIZE (
                ALLSELECTED(shipreadings), shipreadings[SailingKey],
                "Sales_Calc", [Metric] 
            ),
            [Sales_Calc]
        ), 2
    ) 
)

This is how the formula behaves now:

Example when I filter for Itinerary A-B

showing the median of the filtered rows

itinerary	SailingKey	metric	Median of Metric
A-B	1234	10	12
A-B	2345	25	12
A-B	3456	12	12

Example when I do not filter: 

Showing the median of all the rows, not per itinerary

itinerary	SailingKey	metric	Median of Metric
A-B	1234	10	15
A-B	2345	25	15
A-B	3456	12	15
C-D	4567	12	15
C-D	5678	30	15
C-D	6789	18	15

What I expect with the formula to happen:

If I do not select any filter, I want the formula to still show median of metric per itinerary (if metric is not blank or 0, in that case the median should not be shown on that row)

itinerary	SailingKey	metric	Median of metric
A-B	1234	10	12
A-B	2345	25	12
A-B	3456	12	12
C-D	4567	12	18
C-D	5678	30	18
C-D	6789	18	18

like having the median frozen, regardless of other filters on the page. 

Thanks, Michella

[Michella · Accepted solution]

Hi @Ibendlin.

I have found a solution on the internet: 

 CALCULATE (
        MEDIANX( 
            VALUES ( shipreadings[SailingKey]),
            [TotalFuel]
        ),
        ALLEXCEPT( dimsailing, dimsailing[itinerary] )
    )

[lbendlin]

They have crossfiltering enabled

please provide your reasoning for that decision

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
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.

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