Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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)
Solved! Go to 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
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
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
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
)
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
Any updates from anyone? Thanks!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |