Forum Discussion
Percentage from two measures
I have created to two measures in my measure table for the Total number of tickets created and the total number of tickets closed. I created another measure using the above mentioned measure to calculate the percentage of closed tickets. I keep zero...it should be 95%. I even used a quick measure to create the percentage measure. I have a slicer based on a fiscal datetable. Any idea why this isn't working?
Total Tickets Created % difference from Total Tickets Closed =
VAR __BASELINE_VALUE = [Total Tickets Closed]
VAR __VALUE_TO_COMPARE = [Total Tickets Created]
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
DIVIDE(__VALUE_TO_COMPARE, __BASELINE_VALUE)
)
Thank you
Hi HMMFPUA,
The issue is mainly the calculation in the measure.
Right now you're doing:
DIVIDE([Total Tickets Created], [Total Tickets Closed])
which gives:
5781 / 5541 = 104.33%
If the requirement is "what percentage of created tickets have been closed", then the calculation should be:
Total Tickets Closed / Total Tickets Created
So I would simplify the measure to:
Closed Tickets % =
DIVIDE(
[Total Tickets Closed],
[Total Tickets Created],
0
)With the numbers shown in the screenshot:
5541 / 5781 = 95.85%
So you should get approximately 95.85%, rather than 0%.
If you actually want the percentage difference between Created and Closed, then use:
Ticket Difference % =
DIVIDE(
[Total Tickets Created] - [Total Tickets Closed],
[Total Tickets Created],
0
)That would give approximately 4.15%, meaning 4.15% of the created tickets are still not closed.
I would also check the formatting of the measure and set it to Percentage with the required number of decimal places.
The fiscal date slicer shouldn't be a problem as long as both [Total Tickets Created] and [Total Tickets Closed] are being filtered by the same date relationship.
6 Replies
- ShahRukhSameer
Solution Sage
Hi HMMFPUA,
The issue is mainly the calculation in the measure.
Right now you're doing:
DIVIDE([Total Tickets Created], [Total Tickets Closed])
which gives:
5781 / 5541 = 104.33%
If the requirement is "what percentage of created tickets have been closed", then the calculation should be:
Total Tickets Closed / Total Tickets Created
So I would simplify the measure to:
Closed Tickets % =
DIVIDE(
[Total Tickets Closed],
[Total Tickets Created],
0
)With the numbers shown in the screenshot:
5541 / 5781 = 95.85%
So you should get approximately 95.85%, rather than 0%.
If you actually want the percentage difference between Created and Closed, then use:
Ticket Difference % =
DIVIDE(
[Total Tickets Created] - [Total Tickets Closed],
[Total Tickets Created],
0
)That would give approximately 4.15%, meaning 4.15% of the created tickets are still not closed.
I would also check the formatting of the measure and set it to Percentage with the required number of decimal places.
The fiscal date slicer shouldn't be a problem as long as both [Total Tickets Created] and [Total Tickets Closed] are being filtered by the same date relationship.
- krishnakanth240
Super User
Hi HMMFPUA
You can confirm the formula as per requirement DIVIDE([Total Tickets Closed], [Total Tickets Created]) for a % closed metric. If it still shows 0% then drop all 3 measures into table visual and add fiscal slicer to check row by row whether 2 base measures return non-zero values in same filter context. If they do but % measure still shows 0 then issue is context transition inside measure, if base measures go blank then it's relationship issue between fact table and fiscal date table.
- LumericVisuals
Helper II
Since you're getting a literal 0 rather than a blank cell, the ratio itself probably isn't the problem — one of your two measures is likely evaluating to 0 in the filtered context. A common cause when a fiscal date slicer is involved: if both a "created date" and a "closed date" column relate to the same date table, Power BI can only keep one relationship active, so the measure built on the inactive side won't respond to the slicer as expected.
Worth isolating directly — drop both measures into two plain cards next to your percentage measure, under the same fiscal-date slicer, and see which one actually goes to 0. If it's the closed-tickets side, you'd need CALCULATE(..., USERELATIONSHIP('FiscalDate'[Date], Tickets[ClosedDate])) inside that measure to make the slicer filter it correctly.
- rajendraongole1
Hi HMMFPUA -I would not simply repeat the same Closed / Created suggestion. There is an important point worth adding: with the numbers shown, the DAX they posted should not return 0%. It should return 104.33%. That means there may be a second issue beyond the calculation itself.
Closed Tickets % = DIVIDE( [Total Tickets Closed], [Total Tickets Created],0 )
If the Closed measure changes unexpectedly or becomes blank/zero when the fiscal slicer is applied, then I would investigate the date relationships. For example, if Created Date and Closed Date are both related to the same Fiscal Date table, one relationship may be inactive. In that case, the Closed Tickets measure may need something along these lines:
Total Tickets Closed = CALCULATE( COUNTROWS(Tickets), USERELATIONSHIP( 'Fiscal Date'[Date], Tickets[Closed Date] ) )
One correction I'd make to the other replies: “context transition inside the measure” isn't the first thing I'd suspect here. Context transition occurs through CALCULATE/iterators; a straightforward DIVIDE() of two measures doesn't by itself create a context-transition problem. The date relationship/filter context is much more relevant given the fiscal slicer.
Hope this helps.
v-saisrao-msftCommunity Support
Hi HMMFPUA,
Have you had a chance to review the solution shared by krishnakanth240 ShahRukhSameer LumericVisuals rajendraongole1 ? If the issue persists, feel free to reply so we can help further.
Thank you.
- v-saisrao-msft
Hi HMMFPUA,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
