0

I'm using this DAX to dynamically display sales value if I select sales and so on my question is I want to display sales in "Millions" and Orders should be completely displayed how can I define unit formatting in this DAX?

here "SalesValue","SalesQuantity" etc are my buttons for e.g. when i select "SalesValue" button it is showing [Current Sale] and so on i just want to display "SalesValue" and "OrdersValue" in "Millions" otherwise "OrderValue" and "OrderQuantity" should be completely displayed by using in one DAX for e.g. "Actual"

Actual = SWITCH(TRUE(),     VALUES('Measure Dimension'[Measure]) = "SalesValue",[Current Sale],
    VALUES('Measure Dimension'[Measure]) = "SalesQuantity", [Current Sale Quantity],
    VALUES('Measure Dimension'[Measure]) = "OrderValue", [Current Orders],
    VALUES('Measure Dimension'[Measure]) = "OrderQuantity", [Current Order Quantity],
    VALUES('Measure Dimension'[Measure]) = "ReturnValue", [current return],
    VALUES('Measure Dimension'[Measure]) = "ReturnQuantity", [current return Qty],
    VALUES('Measure Dimension'[Measure]) = "PendingValue", [PendingAmount],
    VALUES('Measure Dimension'[Measure]) = "PendingQuantity", BLANK())

can anybody help me regarding this?

DS_Geek
  • 53
  • 1
  • 10

1 Answers1

1

Use FORMAT and FIXED function in your code.

https://dax.guide/fixed/ https://dax.guide/format/

"FIXED rounded to 100",   FIXED ( [Sales Amount], -2, FALSE )

"Format to 100M", FORMAT ( DIVIDE([Sales Amount],1000000), "#### M" )        )

EDITED:

ConditionalStringFormat = SWITCH(TRUE(),
SELECTEDVALUE('Table'[Routing]) = "aaa", FORMAT([SumOfCurrent], "##M"),
SELECTEDVALUE('Table'[Routing]) = "ccc", FORMAT([SumOfCurrent], "Percent"),
SELECTEDVALUE('Table'[Routing]) = "ddd", FORMAT([SumOfPrev], "Fixed"),
SELECTEDVALUE('Table'[Routing]) = "bbb", FORMAT(DIVIDE([SumOfCurrent],10), "Fixed"),
BLANK()
)

enter image description here

msta42a
  • 3,601
  • 1
  • 4
  • 14
  • Where should I add this code? after Blank()? @msta42a – DS_Geek Jun 07 '21 at 07:47
  • If you want format "SalesValue", then replace in SWITCH [Current Sale] to FORMAT(DIVIDE([Current Sale],1000000), "###M") – msta42a Jun 07 '21 at 08:44
  • What exactly is not working? Look at my updated answer. – msta42a Jun 08 '21 at 05:59
  • @m sta42a but it's only working on card and table format not on Bar chart – DS_Geek Jun 08 '21 at 06:31
  • @m sta42a but it's only working on card and Table chart not on Bar chart. plz go through my updated link I hope you will better understand what I'm trying to say. https://stackoverflow.com/questions/67871299/display-unit-formatting-dax-in-powerbi – DS_Geek Jun 08 '21 at 06:34
  • Ah, you don't write that you want charts! The problem here is that FORMAT is returning a single string value and the chart cant rely on a string, unfortunately; You can try with some "dirty" solution -> use multiple charts with measures (formating on "Measure Tool") and hide it conditional on bookmarks. – msta42a Jun 08 '21 at 07:04