Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Keith3618
Frequent Visitor

Dynamic Formatting Help (pictures and DAX included)

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.

 

Units.PNGDollars.PNG

 

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)

1 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")

22.PNG

 

So try this:

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

 

Result

23.PNG

 

here is pbix, please try it.

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lili6-msft
Community Support
Community Support

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:

3.PNG

 

 

----------

 

4.PNG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

Error.PNG

 

 

 

 

 

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
)

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")

22.PNG

 

So try this:

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

 

Result

23.PNG

 

here is pbix, please try it.

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Keith3618
Frequent Visitor

Any updates from anyone? Thanks!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.