Changing dynamic measures' formatting and keeping it a number

[Anonymous]

I find it hard to believe this but Power BI is a MS product and I heard this is common...

Issue: Formatting a number with a number mask transforms it in a string, which in turns restrics the use of charts and data bars (matrix).

Today I found out that when changing the data format of a dynamic measure for simple visualisation purposes and practicity on the business user side, I hinder other functionalities.

Scenario: I am building a Planned vs Actual dashboard, where I can analyse $ currency, # amount, % premium, etc. I have 2 measures(Actual & Planned), that change their outcome based on a VAR value that is selected in a filter by the end user. See example below for actual (Planned is exac. same):

Actual =
VAR MySelection = SELECTEDVALUE(<Measure>)
RETURN
SWITCH ( TRUE(),
MySelection = "Cost", FORMAT([$ Actual Cost],"$#,##0.0")
.
.
.
)

The downside of that approach is when using format the outcome turns out a string. This causes great grief as I cannot use charts or data bars in a matrix as PBI needs a value/number to do so.

I have tried calculated groups, changing the data type in the background, etc and no luck. I simply cannot change the format of a parameter within a measure with multiple measures. My end goal is to have the outcome data, formatted the way I want it and still be able to use it elsewhere as a number

I appreciate any suport. But since this problem seems to be open since 2017, I have little hope on getting this resolved.

Eddie

[Anonymous]

Is this it, Power BI is this limited? Any expert from the community can provide a practical solution to this?

This has been a request since 2017, it is hard to believe there has been no improvement since then...

[Anonymous]

Hi @amitchandak, thanks for your reply. I have tried calculation groups before. I watched all the videos and spent considerable time on it and it does not seem to have the right use for this. Calculation groups seems ideal only for time intelligence?

Perhaps you could guide me on how this would work in a simple matrix with Date rows and Planned, Actual columns responsive to a single filter (lets say total cost, total amount) showing, most importantly, dynamically formatted numbers

Eddie

[V-lianl-msft]

Hi @Anonymous ,

Create different measures in the calculation group and format them.

Create another measure slicer.

Result

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft thanks for your input. 

This might work for a simple time intelligence manipulation of measures but I am sure it does not work for multiple measures as it is described in the problem with the Actual vs Planned.

I am still working through this and so far PBI does not have a simple solution to this simple formatting issue.

I will update this topic if I have a breakthrough.

Eddie

[amitchandak]

@Anonymous , I think with calculation group using the tabular editor you might be able to do that. Please check these two

https://www.youtube.com/watch?v=vlnx7QUVYME

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/