Percentage Calculation from 2 tables

[zeke101]

Hey Guys,

I'm requesting some help on building this measure....

I have 2 tables (an Events table and Rejects Table).

The Events table contains the total number of events that occurred for a certain location, time period, client, etc. 

Events Table Example:

The Rejects table contains the count Rejects of the Events in the table above along with additional information such as what queue it was rejected the reject reason, etc. 

Rejects Table Example:

All I'm trying to accomplish is getting the Rejects as a % of total Event volume. 

I thought it would be as simple as dividing Rejects Count / Event Count.

Here's what I created....

Rejects as a % of Event Volume = CALCULATE(sum(Rejects_All2[COUNT(*)])/CALCULATE(sum('Events All'[COUNT(*)])))

But the calc is giving me an "infinity" %

Any idea on what I'm doing wrong with the calc?

[zeke101 · Accepted solution]

I did a little bit of research and found a measure that works.....

Rejects as a % of Event Volume =

DIVIDE(

'Key Measures'[Total Reject Vol],

CALCULATE(Sum('Events All'[COUNT(*)]),FILTER('Events All','Events All'[Client ID & Site ID]=SELECTEDVALUE(Rejects_All2[Client ID & Site ID]))))

[VahidDM]

Hi @zeke101 

If you want to find the % of Rejectes based on the All Events, Try this:

Measure = COUNTROWS(Rejects_All2)/COUNTROWS(Events All)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[zeke101]

Hi @VahidDM 

This wont work as it only counts the rows of the table, but each row value is worth more than 1.  Notice how the tables have a "Count (*)" column. Those numbers need to be aggregated and then a calculation must be derived to create the percentage. 

[VahidDM]

Hi @zeke101 

Is there any relationship between those tables? if yes, which columns are connected to each other?

Try this measure:

Rejects as a % of Event Volume =
Var _ALL =SUM(Rejects_All2[COUNT(*)])
Var _REJ = SUM('Events All'[COUNT(*)])
return
_REJ/_ALL

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[zeke101]

Hi @VahidDM 

Unfortunately, this measure did not work - Calc is still giving me an "infinity" %

I dont have a relationship between the 2 tables. Here is what the relationship Model looks like: