Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carlol
Helper IV
Helper IV

median -- ignore Slicer Selection

this Measure get me the median based on Slicer Selection, Country

IF ( ROUND(MEDIAN(vwKPIMedianDoorToCT[Minutes]),1) = BLANK (), 0, ROUND(MEDIAN(vwKPIMedianDoorToCT[Minutes]),1) )

 

I want to ignore the country , and also return the median for all countries , for comparison 

?? -- 

ROUND(MEDIAN( Calculate(vwKPIMedianDoorToCT[Minutes],ALLSELECTED ) ),1)
 
thanks
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, 

According to your description, I can roughly understand your requirement, you can try this measure:

Median =

var _value=MEDIANX(ALL(vwKPIMedianDoorToCT),[Minutes])

return

IF(_value=BLANK(),0,ROUND(_value,1))

Then you can create a slicer and a card chart to place this measure, and you will find that the measure will always display the value of the total median regardless of the selection of the slicer:

vrobertqmsft_0-1636943425227.png

 

And you can get what you want.

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

View solution in original post

8 REPLIES 8
v-robertq-msft
Community Support
Community Support

Hi, 

According to your description, I can roughly understand your requirement, you can try this measure:

Median =

var _value=MEDIANX(ALL(vwKPIMedianDoorToCT),[Minutes])

return

IF(_value=BLANK(),0,ROUND(_value,1))

Then you can create a slicer and a card chart to place this measure, and you will find that the measure will always display the value of the total median regardless of the selection of the slicer:

vrobertqmsft_0-1636943425227.png

 

And you can get what you want.

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

carlol
Helper IV
Helper IV

This does not appear to be working ,

 

If I select all countries , 2021 Q1 Median is 75

If I select 1 country , (I want to igore this slicer) , it is 30 , ( I want to illustrate 75 regardless of country selection) but to still filter based on Date

 

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ),ALLSELECTED (), DimDate)

If you want to ignore a slicer, then you need to use ALL instead of ALLSELECTED.

 

See if this is any closer:

CALCULATE (
    MEDIAN ( vwKPIMedianDoorToCT[Minutes] ),
    ALL ( vwKPIMedianDoorToCT ),
    DimDate
)

 

carlol
Helper IV
Helper IV

thanks

 

Looks simple when coded

carlol
Helper IV
Helper IV

Great thanks 

 

Looks simple when written !

AlexisOlson
Super User
Super User

I'd try writing it like this:

VAR MedianAllCountries =
    CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED () )
RETURN
    IF ( ISBLANK ( MedianAllCountries ), 0, ROUND ( MedianAllCountries, 1 ) )

Great thanks 

 

If I want to ignore all slicer but the Date dimension , having issues with  e[Quarter]))) brackets 

 

VAR MedianNational =
CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ),FILTER(ALLEXCEPT(DimDate,DimDate[CurrentYear],DimDate[Quarter])))
RETURN
IF ( ISBLANK ( MedianNational ), 0, ROUND ( MedianNational , 1 ) )

You can add back in the tables you want to keep as filter context like this:

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED (), DimDate )

 

Alternatively, use ALLSELECTED more narrowly only on the things you want to remove local filter context for. For example,

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED ( Slicer[Country] ) )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors