Forum Discussion
Dax error
Hello guys,i created previous month percentage measures for this cards but the second and the third cards are showing outrageous percentages like -97% instead of 2,4,5-10% which is a bit messy,what is the way out guys
The link below is my attached pbix file
https://drive.google.com/file/d/1Mi74b1Ryrw_6vucvpBTxVcASLl_JFG_V/view?usp=drivesdk
hello Diportee97
i might be wrong but i think [Avg Satisfaction pm] and [Avg Wait Time pm] are taking value from wrong measure.
as you can see below, those pm value have all same value because they are using same exact DAX and same measure.
[Patient Visit pm] is using [Patient Visit] which i think this is correct
[Avg Satisfaction pm] is using [Patient Visit] instead of using [Avg Satisfaction Score]
[Avg Wait Time pm] is using [Patient Visit] instead of using [Avg Wait Time]
if i change the measure to the corresponding measure, i think the value looks more sensible.
Hope this will help.
Thank you.
4 Replies
Hey there!
If I understood correctly, you are calculating previous month percentage changes for your cards, but the second and third cards are displaying incorrect percentages, such as -97% instead of expected values like 2%, 4%, 5%-10%.
Here are some new DAX formulas for you to try:
1. Measure for Previous Month Value:
PreviousMonthValue =
CALCULATE(
SUM('Table'[MetricColumn]),
PREVIOUSMONTH('DateTable'[Date])
)2. Measure for Percentage Change:
PercentageChange =
VAR CurrentValue = SUM('Table'[MetricColumn])
VAR PrevValue = CALCULATE(SUM('Table'[MetricColumn]), PREVIOUSMONTH('DateTable'[Date]))RETURN
IF(
ISBLANK(PrevValue) || PrevValue = 0,
BLANK(), -- Avoid dividing by zero
DIVIDE(CurrentValue - PrevValue, PrevValue, 0)
)Hopefully these formulas will work because it will handle division by zero which ensures previous month values exist before calculating percentages. It will correctly retrieves previous month values by using PREVIOUSMONTH() with a properly structured Date Table. And it fixes incorrect signs (e.g., -97%) by ensuring logical calculations.
Hope this helps!
😁😁
I don't really understand your Dax and I don't think it really tally with my already established Dax,I would love if you can view and check the pbix file i sent earlier
hello Diportee97
i might be wrong but i think [Avg Satisfaction pm] and [Avg Wait Time pm] are taking value from wrong measure.
as you can see below, those pm value have all same value because they are using same exact DAX and same measure.
[Patient Visit pm] is using [Patient Visit] which i think this is correct
[Avg Satisfaction pm] is using [Patient Visit] instead of using [Avg Satisfaction Score]
[Avg Wait Time pm] is using [Patient Visit] instead of using [Avg Wait Time]
if i change the measure to the corresponding measure, i think the value looks more sensible.
Hope this will help.
Thank you.
Hi Diportee97 ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
