Forum Discussion

Rymatt830's avatar
Rymatt830
Advocate II
10 years ago
Solved

Change Measure Format

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(AuditNumber...
  • Twan's avatar
    10 years ago

    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")