Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, i have a measure which shows change in percentage over time. However i am currenlty getting a value of -0.00% (its due to a rounding issue as the percentage change is -0.0000000029% howvere i am only displaying to 2 DP. The measure is as below:
Could osmeone help me either change the measure to eliminate this issue (id like it to just show 0.00% change) or come up with a clever idea on how to stop this.
Thanks
You could add an IF statement
% Change Over Time =
CALCULATE (
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( DQRule[PersonID] ),
FILTER ( DQRule, DQRule[Pass/Fail] = 1 )
),
CALCULATE ( DISTINCTCOUNT ( DQRule[PersonID] ) )
)
)
-
VAR EarlyDate =
MIN ( DQRule_Timeline_Rules[DateRun] )
VAR __Result =
CALCULATE (
DIVIDE (
SUM ( 'DQRule_Timeline_Rules'[Pass] ),
SUM ( 'DQRule_Timeline_Rules'[Total Execution Population] )
),
DQRule_Timeline_Rules[DateRun] = EarlyDate
)
RETURN
IF ( __Result < 0.004, 0, __Result )
0.004 because then rounded to two decimal places it is 0.00
Thank you for this suggestion. I have tried the below and i am getting what i think is an incorrect result. i would epect any negative values to disply 'Less than 0' ? hoever i am getting -2.99E-7 as the reuslt (or -0.00% when i set it to percentage) Any suggestions as how to get over this?
Maybe you could change the last line to be -0 ?
IF(_Result < -0,"Less than 0",_Result))
Hi @Anonymous
try the ROUND function
% Change Over Time =
CALCULATE (
DIVIDE (
CALCULATE (
DISTINCTCOUNT ( DQRule[PersonID] ),
FILTER ( DQRule, DQRule[Pass/Fail] = 1 )
),
CALCULATE ( DISTINCTCOUNT ( DQRule[PersonID] ) )
)
)
-
VAR EarlyDate =
MIN ( DQRule_Timeline_Rules[DateRun] )
RETURN
ROUND (
CALCULATE (
DIVIDE (
SUM ( 'DQRule_Timeline_Rules'[Pass] ),
SUM ( 'DQRule_Timeline_Rules'[Total Execution Population] )
),
DQRule_Timeline_Rules[DateRun] = EarlyDate
),
2
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |