Formatting and results between two measures not consistent

[PaulaSims]

Hello,

I'm new to PowerBI so I apologize if I don't word this question correctly. I don't have access to PBI personally (only through work) so I'll have to post an example since I can't post a sample.

I have several columns, key are: Location, Product  Date, Total_Cust, New_Cust. I then have 2 measures for NEW_CUST_CALC. I also have a toggle for Percent & Total. There are also slicers for Date, Location.

Outputs:

1. KPI Card using Measure1 showing decimals only (rules below), slicers for location & date applied

2. Matrix using Measure2 showing either decimals or whole numbers (rules below), slicers for location & date appied.

The requirements for Measure1 (used in a KPI card) are:

1. If result is zero, show 0

2. If results is <0, print # digits to show value (could be 0.01, 0.001. 0.0001, etc)

3. Use dynamic formatting to show the results in decimal form

The requirements for Measure2 (used in a matrix) are:

1. If result is zero, if Percent, print 0%, otherwise 0

2. If results is <0, print # digits to show value (could be 0.01, 0.001. 0.0001, etc)

3. Use dynamic formatting to show the results in either Percent or Total form.

Problems:

1. Sometimes the result for Measure1 does not equal Measure2, off by rounding. Sometimes rounds up, sometimes rounds down. Neither is consistent. Measure1 could be .1 and Measure2 could be .058% or Measure1 = .058 and Measure2 = 1%.

2. Sometimes the results for Measure1 output in scientific notation if the location is switched. So for West = .0058, for East = 5.80E-03

2. For both Measure1 and Measure2 I want the result to show only the digits I need, usually between 2 digits and 4 digits, but always variable (sometimes it could be Measure1 = .001 and Measure2 = .1%, or Measure1 = .00005 and Measure2 = .005% 

Questions:

1. Why the difference, when in essence, they seem like the same calculations?

2. How do I get them to dynamically change in digits (both the matrix version and the one on the KPI card)?

Here are my formulas:

Measure1 //(to be used on a KPI card, filters for location and  date to be applied)

= SUMX(New_Cust)/SUMX(Total_Cust)

Measure2 //(to be used on a Matrix showing Date & Products, slicers for location and date can be applied)

= SWITCH(

      TRUE,

       SELECTVALUE('toggle'[toggle]) = 'Percent' && (SUMX(New_Cust)/SUMX(Total_Cust)) = 0

     ,format(SUMX(New_Cust)/SUMX(Total_Cust),"0%"),

       SELECTVALUE('toggle'[toggle]) = 'Percent' && (SUMX(New_Cust)/SUMX(Total_Cust)) <.0001

     ,format(SUMX(New_Cust)/SUMX(Total_Cust),"0.000##%"),

       SELECTVALUE('toggle'[toggle]) = 'Percent' && (SUMX(New_Cust)/SUMX(Total_Cust)) <.001

     ,format(SUMX(New_Cust)/SUMX(Total_Cust),"0.00##%"),

       SELECTVALUE('toggle'[toggle]) = 'Percent' && (SUMX(New_Cust)/SUMX(Total_Cust)) <.01

     ,format(SUMX(New_Cust)/SUMX(Total_Cust),"0.0##%"),

       SELECTVALUE('toggle'[toggle]) = 'Percent' && (SUMX(New_Cust)/SUMX(Total_Cust)) <.1

     ,format(SUMX(New_Cust)/SUMX(Total_Cust),"0##%"),

       SELECTVALUE('toggle'[toggle]) = 'Total'      ,format(SUMX(New_Cust),"##0%"))

Thank you so much for your help

Paula

[saud968 · Accepted solution]

1. Why the difference, when in essence, they seem like the same calculations?
The differences arise because of the different formatting rules applied to each measure. Measure1 is formatted to show decimals only, while Measure2 uses a SWITCH statement to dynamically format the result as either a percentage or a total, depending on the toggle selection. This can lead to rounding differences and inconsistencies in how the values are displayed.

2. How do I get them to dynamically change in digits (both the matrix version and the one on the KPI card)?
To achieve consistent dynamic formatting for both measures, you can modify your DAX formulas to ensure they handle the formatting in a similar way. Here's a revised approach:

Measure1 (KPI Card)
Measure1 =
VAR Result = SUMX(New_Cust) / SUMX(Total_Cust)
RETURN
IF(Result = 0, 0, FORMAT(Result, "0.####"))
Measure2 (Matrix)
Measure2 =
VAR Result = SUMX(New_Cust) / SUMX(Total_Cust)
RETURN
SWITCH(
TRUE,
SELECTVALUE('toggle'[toggle]) = "Percent" && Result = 0, "0%",
SELECTVALUE('toggle'[toggle]) = "Percent" && Result < 0.0001, FORMAT(Result, "0.000##%"),
SELECTVALUE('toggle'[toggle]) = "Percent" && Result < 0.001, FORMAT(Result, "0.00##%"),
SELECTVALUE('toggle'[toggle]) = "Percent" && Result < 0.01, FORMAT(Result, "0.0##%"),
SELECTVALUE('toggle'[toggle]) = "Percent" && Result < 0.1, FORMAT(Result, "0##%"),
SELECTVALUE('toggle'[toggle]) = "Total", FORMAT(SUMX(New_Cust), "##0"),
FORMAT(Result, "0.####")
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!