Change Measure Format

[Rymatt830]

I am unable to change the format of a measure. The Formatting section of the Modeling tab only allows Format: Text to be selected. Here's the measure:

NHSSVUNNetAssets = CALCULATE(MEDIAN(AuditNumbers[UnrestNA]),AuditNumbers[PCODE]="8217")

It's possible I am using the wrong DAX. For example, Median is not necessary because the measure filters to one value; however, the measure requires an aggregation. I want the measure displayed as currency. Any help would be great.

[Anonymous]

I found a fix to this was to just put a vlue around the outside 🙂

NHSSVUNNetAssets = Value(CALCULATE(MEDIAN(AuditNumbers[UnrestNA]),AuditNumbers[PCODE]="8217"))

[Anonymous]

Why is this marked "Solved?" There is still a bug in Median aggregation which prevents numbers from being formatted as anything other than text. 

[Aracon]

The bug is still there

[Greg_Deckler]

Try SUM or MAX instead of MEDIAN

[Twan · Accepted solution]

That is very strange.  It looks like is a bug with the MEDIAN() function.  If you change your measure to use AVERAGE() instead of MEDIAN() it allows you to format the measure in formats besides Text.

One way to get around this bug would be to calculate the median without using the MEDIAN() function.  SQL BI has a great desctiption of how you would write your own MEDIAN measure in DAX.

It would look like this for your model:

Custom Median :=
(
    MINX (
        FILTER (
            VALUES ( AuditNumbers[UnrestNA] ),
            CALCULATE (
                COUNT ( AuditNumbers[UnrestNA] ),
                AuditNumbers[UnrestNA]
                    <= EARLIER ( AuditNumbers[UnrestNA] )
            )
                > COUNT ( AuditNumbers[UnrestNA] ) / 2
        ),
        AuditNumbers[UnrestNA]
    )
        + MINX (
            FILTER (
                VALUES ( AuditNumbers[UnrestNA] ),
                CALCULATE (
                    COUNT ( AuditNumbers[UnrestNA] ),
                    AuditNumbers[UnrestNA]
                        <= EARLIER ( AuditNumbers[UnrestNA] )
                )
                    > ( COUNT ( AuditNumbers[UnrestNA] ) - 1 ) / 2
            ),
            AuditNumbers[UnrestNA]
        )
) / 2

Then change your measure to use the custom median measure and you will be able to change the format of this measure to currency.

NHSSVUNNetAssets = CALCULATE([Custom Median] ,AuditNumbers[PCODE]="8217")

[Rymatt830]

Thank you for your help, Twan. Using AVERAGE() instead of MEDIAN() allows the format to be changed. Also, thank you for including a reference to the custom function.