The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello Everyone,
I'm looking for some help in performing a calculation in Power BI.
Raw data is an excel spreadsheet with extracted Salesforce data - and the columns I care about in this calculation are as follows:
What i'm trying to calculate is:
The total number of cases where both the Case Age and Reopened values are both 0; and take that number and divide it over the total number of cases that an agent handled (which could be the count of rows for either of the columns in question as each row would represent a case regardless).
For example:
Agent handled 100 cases this month; 3 were reopened and 86 were closed the same day (case age is 0). I want the result returned to be: 83 (Number of cases closed on the same day and were not reopened) divided over 100 (total cases handled) = 83%.
I appreciate any help in advance!
Solved! Go to Solution.
Var bothZero =
Calculate(
Countrows( table ),
Table[Case Age] = 0,
Table[Reopened] = 0
)
Return
Divide(
AllZero,
Countrows( table )
)
Hi @rgonzalez_rpra ,
I guess, you need to create the measures as per my understanding:
1. Total Cases Handled - DISTINCTCOUNT(Cases[CaseID])
2. Cases Closed Same Day, Not reopened -
ClosedSameDayNotReopened =
CALCULATE(
COUNTROWS(Cases),
Cases[CaseAge] = 0,
Cases[Reopened] = 0
)
3. % calculation
Pct_ClosedSameDayNotReopened =
DIVIDE(
[ClosedSameDayNotReopened],
[TotalCases],
0
)
Example:
I handled 10 cases
4 of them have caseage = 0 and reopened = 0
The measure returns 4/10 = 40%
Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
Hi @rgonzalez_rpra,
Thanks for reaching out to the Microsoft fabric community forum.
@maruthisp @Deku Thanks for your Prompt response
@rgonzalez_rpra
I’ve implemented the calculation logic you described(With SampleData), and it works perfectly for the use case!
The measure accurately counts cases where both Case Age = 0 and Reopened = 0, and divides that by the total cases handled. When used in a report with agent-level filtering, it correctly shows the percentage of same-day closed, not reopened cases per agent.
Uploaded the PBIX file here .for ref
If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.
We appreciate your engagement and thank you for being an active part of the community.
Best regards,
LakshmiNarayana.
Hi @rgonzalez_rpra ,
I guess, you need to create the measures as per my understanding:
1. Total Cases Handled - DISTINCTCOUNT(Cases[CaseID])
2. Cases Closed Same Day, Not reopened -
ClosedSameDayNotReopened =
CALCULATE(
COUNTROWS(Cases),
Cases[CaseAge] = 0,
Cases[Reopened] = 0
)
3. % calculation
Pct_ClosedSameDayNotReopened =
DIVIDE(
[ClosedSameDayNotReopened],
[TotalCases],
0
)
Example:
I handled 10 cases
4 of them have caseage = 0 and reopened = 0
The measure returns 4/10 = 40%
Please let me know if you have further questions.
If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X
Var bothZero =
Calculate(
Countrows( table ),
Table[Case Age] = 0,
Table[Reopened] = 0
)
Return
Divide(
AllZero,
Countrows( table )
)
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |