I have a large data set provides business unit sales numbers. I have written dax that will format the numbers as the sales folks drill down on the data set. It will for from Billions to thousands. Here is the DAX:
Act - Plan =
var CurrentValue = ABS(
CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Revenue (Projected)",
DataBase[Period] = "CY Actual"
)+CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Open Order $s",
DataBase[Period] = "CY Actual"
)-CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Revenue (Projected)",
DataBase[Period] = "Baseforecast")
)
Return
Switch (
true (),
CurrentValue <= 1E3, FORMAT ( CurrentValue, "#,0.00" ),
CurrentValue <= 1E6, FORMAT ( CurrentValue, "#,0,.00 K" ),
CurrentValue <= 1E9, FORMAT ( CurrentValue, "#,0,,.00 M"),
FORMAT ( CurrentValue, "#,0,,,.00 B")
)
The issue that I am running in is that the formatted value will not show negative (1-2=1).
what should I include to be able to reflect the proper value?
Thank you for your time!
1
You have ABS wrapped for CurrentValue and this is what you are outputting, so will always be positive.
Try this instead:
Act - Plan =
var CurrentValue = // ABS removed here
CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Revenue (Projected)",
DataBase[Period] = "CY Actual"
) + CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Open Order $s",
DataBase[Period] = "CY Actual"
) - CALCULATE(
SUM(DataBase[Value]),
DataBase[Measure] = "Revenue (Projected)",
DataBase[Period] = "Baseforecast")
)
var absValue = ABS(CurrentValue) // ABS done here for switch conditionals
Return
Switch (
true (),
absValue <= 1E3, FORMAT ( CurrentValue, "#,0.00" ),
absValue <= 1E6, FORMAT ( CurrentValue, "#,0,.00 K" ),
absValue <= 1E9, FORMAT ( CurrentValue, "#,0,,.00 M"),
FORMAT ( CurrentValue, "#,0,,,.00 B")
)