Forum Discussion
Looking for equivalent of DAX FORMAT function except does NOT convert numbers to text
The DAX below uses calculation IDs from a disconnected table to assign measures to sections of a P&L's actuals. However, the most trivial part of the code i.e., the two references to the FORMAT function, eliminates further calculations, such as variance to plan for the those two line items. For some reason, FORMAT was designed to convert numbers to text, which is the last thing I can imagine ever wanting as no further calculations are possible at that point, which defeats the entire purpose of DAX. Is there an equivalent function that allows you to simply apply a custom format to a number and actually have it remain a number?
02.111measure Actuals Complete P&L:=VAR AssignIDsToMeasures = SWITCH(TRUE(),
MAX(dPnLSkeleton[CalculationID]) = 1, [02.101m CC Actuals Revenue],
MAX(dPnLSkeleton[CalculationID]) = 2, [02.102m CC Act CoR],
MAX(dPnLSkeleton[CalculationID]) = 3, [02.103m CC Act Gross Profit],
MAX(dPnLSkeleton[CalculationID]) = 4, FORMAT([02.104m CC Act Gross Profit %] , "0.0%") ,
MAX(dPnLSkeleton[CalculationID]) = 5, [02.105m CC Act Opex S&M],
MAX(dPnLSkeleton[CalculationID]) = 6, [02.106m CC Act Opex R&D],
MAX(dPnLSkeleton[CalculationID]) = 7, [02.107m CC Act Opex G&A],
MAX(dPnLSkeleton[CalculationID]) = 8, [02.108m CC Act Opex S&M, R&D, G&A],
MAX(dPnLSkeleton[CalculationID]) = 9, [02.109m CC Act EBITDAR],
MAX(dPnLSkeleton[CalculationID]) = 10, FORMAT([02.110m CC Act EBITDAR %], "0.0%") )
RETURN
IF (AssignIDsToMeasures = 0, BLANK(),
AssignIDsToMeasures)
8 Replies
- amitchandak
Super User
sparta1000 , better you opt for calculation group
https://www.sqlbi.com/articles/dynamic-format-strings-with-calculation-groups/
Thank you for the quick response, Amitchandak. I very much appreciate it. I didn't understand any of the concepts in the article or video but will begin investigating Tabular Editor and calculation groups going forward. In the meantime, I'll post if I find a simple solution to what should be a trivial problem. I also should have mentioned this solution is required for PowerPivot not Power BI.
- tamerj1
Community Champion
Hi sparta1000
please try
02.111measure Actuals Complete P&L := VAR Selection = MAX ( dPnLSkeleton[CalculationID] ) VAR AssignIDsToMeasures = SWITCH ( Selection, 1, [02.101m CC Actuals Revenue], 2, [02.102m CC Act CoR], 3, [02.103m CC Act Gross Profit], 4, [02.104m CC Act Gross Profit %], 5, [02.105m CC Act Opex S&M], 6, [02.106m CC Act Opex R&D], 7, [02.107m CC Act Opex G&A], 8, [02.108m CC Act Opex S&M, R&D, G&A], 9, [02.109m CC Act EBITDAR], 10, [02.110m CC Act EBITDAR %] ) RETURN IF ( AssignIDsToMeasures <> 0, IF ( Selection IN { 4, 10 }, FORMAT ( AssignIDsToMeasures, "0.0%" ), AssignIDsToMeasures ) )Thank you tamerj1 -- your solution looks great! I'll test it out later this morning and post an update. I should mention that users don't select any of these line items as the entire framework must be present at all times for the P&L statement to appear in its entirety -- so not sure if that makes a difference but I'll test.
Thank you tamerj1 -- I like your thought process with the code, but I didn't explain the issue very well. Your code appears to apply to typical scenarios where users will be making different selections. In my case, there are no selections; everything's set in stone from a report line item perspective.
Nevertheless, the whole issue is how to get around FORMAT's inexplicable conversion of numbers to text. If FORMAT didn't change the data type to text, the simple variance math would be executed and there would be no issue. That's why this is so frustrating.
To make the issue as transparent as possible, I'll isolate the behavior of the Variance % measure that is driven by earlier measures that contain FORMAT. The Variance % measure materializes two line items on the visual that must be formatted as % and yet ideally still behave like numbers. But because FORMAT in supporting measures converts numbers to text, in order to avoid a DAX 'can't do math on text' error message, the Variance % measure is currently written to effectively blank out (via empty string) the two problematic line items.
Below is the the code for the Variance % measure that produces the report above.
07.311m Var % Act vs Bud:=SWITCH (TRUE(),
MAX( dPnLSkeleton[CalculationID]) = 1, [07.301m CC Var % Act Rev Less Threshold Rev],
MAX( dPnLSkeleton[CalculationID]) = 2, [07.302m CC Var % Threshold CoR Less Act CoR],
MAX( dPnLSkeleton[CalculationID]) = 3, [07.303m CC Var % Act Gross Profit Less Threshold Gross Profit],
MAX( dPnLSkeleton[CalculationID]) = 4, "",
MAX( dPnLSkeleton[CalculationID]) = 5, [07.305m CC Var % Threshold S&M Less Act S&M],
MAX( dPnLSkeleton[CalculationID]) = 6, [07.306m CC Var % Threshold R&D Less Act R&D],
MAX( dPnLSkeleton[CalculationID]) = 7, [07.307m CC Var % Threshold G&A Less Act G&A],
MAX( dPnLSkeleton[CalculationID]) = 8, [07.308m CC Var % Threshold Opex SMRDGA Less Act Opex SMRDGA],
MAX( dPnLSkeleton[CalculationID]) = 9, [07.309m CC Var % Act EBITDAR Less Threshold EBITDAR],
MAX( dPnLSkeleton[CalculationID]) = 10, "")
So I'm still left wondering how to get around FORMAT's conversion of numbers to text in the supporting measures so that the Variance % measure works for all line items... and no more empty string workaround or "can't do math on text" error.- tamerj1
There is no FORMAT function in this measure, however, the empty sting "" will generate error when performing calculations. When referring to this measure in other formulas you need to wrap it with VALUE function. This will eliminate the effect of the empty string ""
However, unless you want to force showing the blank cell in your matrix (which Idon't think is required in your case) I would suggest just to not use the empty string in your formula. In fact the whole condition is not require as the result will be blank by default.
07.311m Var % Act vs Bud := SWITCH ( MAX ( dPnLSkeleton[CalculationID] ), 1, [07.301m CC Var % Act Rev Less Threshold Rev], 2, [07.302m CC Var % Threshold CoR Less Act CoR], 3, [07.303m CC Var % Act Gross Profit Less Threshold Gross Profit], 5, [07.305m CC Var % Threshold S&M Less Act S&M], 6, [07.306m CC Var % Threshold R&D Less Act R&D], 7, [07.307m CC Var % Threshold G&A Less Act G&A], 8, [07.308m CC Var % Threshold Opex SMRDGA Less Act Opex SMRDGA], 9, [07.309m CC Var % Act EBITDAR Less Threshold EBITDAR] )
