Dynamic Formatting Help (pictures and DAX included)

[Keith3618]

Hello Power BI Community. Long time viewer, first time poster. Hoping there is someone out there who can help me achieve my desired result.

I have a Measure Slicer set up that is able to change the value of te Measure being used in my Card visual between Total Units, Total Dollars, and Average Dolalrs.

I am trying to format each value appropriately. Total Units should be a whole number, Total Dollars should be Currency (USD), and Average Dollars should be Currency (USD).

These are the results I am currently getting when trying to Format my DAX measure.

Here is my Dax Code. It appears that the FORMAT() function is not Formatting my Total Dollars value into Currency like I expected. Any help would be very much apprecited! Thank you all!

Measure =
IFERROR(
IF(
    IF(
        HASONEVALUE('Measure Filter'[Measure]),
        SWITCH(
            FIRSTNONBLANK('Measure Filter'[Measure], 'Measure Filter'[Measure]),
                "Total Dollars", FORMAT(SUM('Table'[RequestedAmt]),"Currency"),
                "Total Units",FORMAT(Count('Table'[AppID]),"Whole Number"),
                "Average Dollars", AVERAGE('Table'[RequestedAmt])),
            Blank())=0,
       VALUE("Make Selection"),
    IF(
        HASONEVALUE('Measure Filter'[Measure]),
        SWITCH(
            FIRSTNONBLANK('Measure Filter'[Measure], 'Measure Filter'[Measure]),
                "Total Dollars", FORMAT(SUM('Table'[RequestedAmt]),"Currency"),
                "Total Units",FORMAT(Count('Table'[AppID]),"Currency"),
                "Average Dollars", AVERAGE('Table'[RequestedAmt])),Blank())),
0)

[v-lili6-msft]

hi,@Keith3618

       Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[v-lili6-msft]

hi,@Keith3618

     After my research, I replicate your formula on my own report, it works well. please delete this measure and add this measure again, do not change the new measure by Modeling->Formatting

here is my formula

Measure 3 = 
IFERROR (
    IF (
        IF (
            HASONEVALUE ( 'Measure Dimensions'[Measure] ),
            SWITCH (
                FIRSTNONBLANK ( 'Measure Dimensions'[Measure], 'Measure Dimensions'[Measure] ),
                "Total Sales", FORMAT ( SUM ( Sales[Order Quantity]  ), "Currency" ),
                "Total Costs", FORMAT ( COUNT ( Sales[Order Quantity]  ), "Whole Number" ),
                "Total Profits", AVERAGE ( Sales[Order Quantity]  )
            ),
            BLANK ()
        )
            = 0,
        VALUE ( "Make Selection" ),
        IF (
            HASONEVALUE ( 'Measure Dimensions'[Measure] ),
           SWITCH (
                FIRSTNONBLANK ( 'Measure Dimensions'[Measure], 'Measure Dimensions'[Measure]),
                "Total Sales", FORMAT ( SUM ( Sales[Order Quantity] ), "Currency" ),
                "Total Costs", FORMAT ( COUNT (Sales[Order Quantity] ), "Currency" ),
                "Total Profits", AVERAGE ( (Sales[Order Quantity] )
            )),
            BLANK ()
        )
    ),
    0
)

and result:

----------

Here is my pbix, please try it .

https://www.dropbox.com/s/tb9a1951jvosrdp/Visual%20change%20using%20slicer%20%282%29.pbix?dl=0

If it is not your case, please share your pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin

[Keith3618]

Thank you so much for the help. I am very restricted on uploading so I need to verify serveral items first.

I think I'm almost there, just seeing some weird errors (my dax below is a copy and paste of yours above)

Total Dollars works... Total untis is experiencing some trouble to day the least. Also, My charts lose all connection to the Measure when I try to add the FORMAT logic.

Measure 3 =
IFERROR (
    IF (
        IF (
            HASONEVALUE ( 'Measure Filter'[Measure] ),
            SWITCH (
                FIRSTNONBLANK ( 'Measure Filter'[Measure], 'Measure Filter'[Measure] ),
                "Total Dollars", FORMAT ( SUM ( 'Pipeline Detail'[NetExposure]  ), "Currency" ),
                "Total Units", FORMAT ( COUNT ( 'Pipeline Detail'[NetExposure]  ), "Whole Number" ),
                "Average Dollars", AVERAGE ( 'Pipeline Detail'[NetExposure]  )
            ),
            BLANK ()
        )
            = 0,
        VALUE ( "Make Selection" ),
        IF (
            HASONEVALUE ( 'Measure Filter'[Measure] ),
           SWITCH (
                FIRSTNONBLANK ( 'Measure Filter'[Measure], 'Measure Filter'[Measure]),
                "Total Dollars", FORMAT ( SUM ( 'Pipeline Detail'[NetExposure]  ), "Currency" ),
                "Total Units", FORMAT ( COUNT ( 'Pipeline Detail'[NetExposure]  ), "Whole Number" ),
                "Average Dollars", AVERAGE ( 'Pipeline Detail'[NetExposure]  )
            ),
            BLANK ()
        )
    ),
    0
)

[v-lili6-msft · Accepted solution]

hi,@Keith3618

      After my research, the reason is this formula: 

  "Total Units", FORMAT ( COUNT ( 'Pipeline Detail'[NetExposure]  ), "Whole Number" 

For example:

I use it to add a column:

Column = FORMAT(Sales[Total Unit Cost],"Whole Number")

So try this:

"Total Costs", FORMAT ( COUNT ( Sales[Order Quantity] ), 0 )

Result

here is pbix, please try it.

https://www.dropbox.com/s/kv5nhpyo3fedprv/914Visual%20change%20using%20slicer.pbix?dl=0

Best Regards,

Lin

[Keith3618]

Any updates from anyone? Thanks!