Forum Discussion
Change Measure Format
- 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] ) ) / 2Then 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")
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]
)
) / 2Then 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")
- Rymatt83010 years agoAdvocate II
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.