Performing calculation with a few conditions

[rgonzalez_rpra]

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:

• Case Age (calculated as a whole number in days)
• Reopened (data is displayed as '0' for no or '1' for yes)

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!

[Deku · Accepted solution]

Var bothZero =

Calculate(

Countrows( table ),

Table[Case Age] = 0,

Table[Reopened] = 0

)

Return

Divide( 

AllZero,

Countrows( table )

)

[v-lgarikapat]

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.

[maruthisp · Accepted solution]

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

[Deku · Accepted solution]

Var bothZero =

Calculate(

Countrows( table ),

Table[Case Age] = 0,

Table[Reopened] = 0

)

Return

Divide( 

AllZero,

Countrows( table )

)