Difficulty Performing Calculations on Measure that uses FORMAT function

[Anonymous]

I have a single measure that displays either the sum of sales in dollars, or in units depending on which value is selected in a slicer. 

YTD Total = SWITCH([Selected Unit],

   "Quantity", CALCULATE(SUM(Quantity), DATESBETWEEN(Date, YearStart, Today)),

   "Dollars", CALCULATE(FORMAT(SUM(Dollars), "Currency"), DATESBETWEEN(Date, YearStart, Today)))

This measure iself works just fine, but it is used in another measure "YOY Percent Change"

YOY Percent Change = [YTD Total] / [Prior YTD Total]

Where "Prior YTD Total" is a measure similar to "YTD Total".

This measure also works fine in a table, but when placed into a matrix which has "region" in the rows. I get the following error:

"MdxScript(Model) (270,62) Calculation error in measure "YTD Total" Cannot convert value " of type Text to type Numeric."

Thanks for any help on this relatively complex issue.

[Anonymous]

@Anonymous,

What is  the result when you directly drag YTD total measure to Matrix? What DAX formula do you use to calculate Prior YTD Total? I am unable to reproduce the above error using my sample table, you can test my PBIX file.

Could you please share sample data of your tables so that I can reproduce?



Regards,
Lydia

[Anonymous]

Hi,

Thanks for your response, and for taking the time to mock it up in a pbix.

I spent some time mocking up my own case and was able to solve part of the issue.

The remaining issue is that the currency formatted number in your example sorts as if it's a string, instead of a number. Is there an easy solution for this?

Thanks!

[Anonymous]

@Anonymous,

It is actual a number, see the total value of the matrix visual.


Regards,
Lydia

[Anonymous]

@Anonymous

The total appears to be correct, but the sort order is not. See the below image, where the numbers are sorted by their leading digit, instead of their value. Here's the pbix I used to mock this up.

Thanks again!

Nick

[Anonymous]

@Anonymous,

A method is to create a measure using DAX  below, then sort by the measure in the table visual.

Measure 2 = IF(SELECTEDVALUE(Table3[Measure],"")="Dollars",RANKX(ALLSELECTED(Table4[Region]),CALCULATE(SUM(Table4[Dollars]),DATESBETWEEN(Table4[Date],MAX(Table4[YearStart]),TODAY())),,ASC,Dense))

Regards,
Lydia

[Anonymous]

Hi Lydia,

Thanks again for your response. Does this solution require creating a new visible 'sort measure' for each other measure? This isn't ideal because we have 5 measures in the table, and I don't want to add 5 additional columns to the table to enable sorting.

If a measure is only of type currency, I am able to sort by value. Why do we lose this ability when the measure switches between currency and decimal number?

Thanks!

Nick

[Anonymous]

@Anonymous,


 “Why do we lose this ability when the measure switches between currency and decimal number?”

The issue is caused by that you are mixing different data formats in the YTD Total measure as Power BI can't recognize them. For the measures like currency values in YTD Total, please create sort measure.

Regards,
Lydia