Stacked Chart Label Formatting with and without decimal places using measure based on slicer choice

[dharsanj]

Hi there,

I have a stacked chart where I want to show the data labels with and without decimal places based on a slicer selection. If the slicer selection is $Amount, then I want to show the values with no decimals; if the slicer selection is %Rev, then I want to display one decimal place. I have tried using FORMAT, and CONVERT options on the measure. The measure I am using is below:

VAR TotalRev = CALCULATE ( SUM ( 'Waterfall'[FullyAllocatedValue (ABS)] ), ALL ( 'Waterfall' ), 'Waterfall'[PnL-Lvl 3 (Stacked Chart)] = "Revenue", VALUES ( 'Waterfall'[PMBU Lvl 3] ), //'Trended Quarter and Versions'[Version] = VALUES ( TblParamCurrQRF[CurrQRF] ), VALUES ( Waterfall[Quarter Version] ) ) VAR PctWaterfallAmt_ABS = DIVIDE ( [WaterfallAmt (ABS)], TotalRev ) VAR Choice = SELECTEDVALUE ( 'CommonSizeChoice'[Choice] ) RETURN SWITCH ( Choice, "$Amount", CONVERT ( CALCULATE ( [WaterfallAmt (ABS)], 'Waterfall'[PnL-Lvl 3] <> "Revenue", 'Waterfall'[PnL-Lvl 3] <> "Direct Margin", 'Waterfall'[PnL-Lvl 3] <> "Gross Margin" ), INTEGER ), "%Rev", CONVERT ( CALCULATE ( PctWaterfallAmt_ABS * 100, 'Waterfall'[PnL-Lvl 3] <> "Revenue", 'Waterfall'[PnL-Lvl 3] <> "Direct Margin", 'Waterfall'[PnL-Lvl 3] <> "Gross Margin" ), DOUBLE ), "Invalid Option" )

However, I am unable to get the desired result. See snapshots below of what I am trying to accomplish. Anyone has any ideas of how to fix this? 

Thanks

Slicer viewIf slicer is $Amount, then amounts should show with NO decimalsIf slicer is %Rev, then amounts should show with ONE decimal 

[dharsanj · Accepted solution]

I am happy to announce that this blog post from Matt Allignton shows a way how to do this via the use of Calculated Groups. The blog post is here:

https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/

After I followed his steps, I was able to accomplish what I wanted to achieve. Thanks Matt!

See snapshots below:

$Amount - No decimals here

%Amount - one decimal place here

[dharsanj]

Thanks. So the only workaround would be set the two views as bookmarked views and set the formats independently?

[v-jingzhang]

Hi @dharsanj 

You could break it into three measures to make it work. You could format [Amount] measure and [Percentage] measure separately via Measure tools > Formatting. Then put these two measures into [Display] measure and use [Display] measure in the column chart. This will get your expected result and no need to use bookmarks.

Amount =
CALCULATE (
    [WaterfallAmt (ABS)],
    'Waterfall'[PnL-Lvl 3] <> "Revenue",
    'Waterfall'[PnL-Lvl 3] <> "Direct Margin",
    'Waterfall'[PnL-Lvl 3] <> "Gross Margin"
)

Percentage =
CALCULATE (
    PctWaterfallAmt_ABS * 100,
    'Waterfall'[PnL-Lvl 3] <> "Revenue",
    'Waterfall'[PnL-Lvl 3] <> "Direct Margin",
    'Waterfall'[PnL-Lvl 3] <> "Gross Margin"
)

Display =
VAR Choice = SELECTEDVALUE ( 'CommonSizeChoice'[Choice] )
RETURN
    SWITCH ( Choice, "$Amount", [Amount], "%Rev", [Percentage], [Amount] )

 

Here is a great guide on how to switch different measures in the same chart:

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[dharsanj]

Hi there,

Thank you for your suggestion. Unfortunately, it didn't work. I setup the 3 measures [DebugAmount] - comma separated and no decimal places, [DebugPercentage] - one decimal place, and then the [SwitchedDebug] which uses the switch to pull either measure into the chart display. The problem is that [SwitchedDebug] format is set to a specific value and that is what is displayed in the chart labels. So I either get whole numbers if I toggle beween Amount or Percentage if I set the format for [SwitchedDebug] to 0 decimal places,  or a decimal number with 1 decimal in the label values if set the [SwitchedDebug] Format to 1 decimal. I played with all different options for [SwitchedDebug] formatting, even keeping it as Whole Number with 0 decimal places as you have highlighed in box #2. What I want is have the labels showing no decimal places if the user chooses the $Amount option or have labels with one decimal place if the user chooses the Percent option.

As @amitchandak mentioned earlier, the final data format is governed by the setting applied for [SwitchedDebug] in measure tools, so it appears that there is no getting around this issue.

Not sure if I am missing something. If I am, would you mind sharing a simple demo PBI file that illustrates your idea? Many thanks for that. 

 

[amitchandak]

@dharsanj , actually your final data format is governed by measure tools. so even if return INT, as the Measure property shows one decimal place will keep on showing that.