Forum Discussion

zeke101's avatar
zeke101
Helper II
4 years ago
Solved

Percentage Calculation from 2 tables

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?

Need Help
  • zeke101's avatar
    zeke101
    4 years ago

    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]))))

5 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      zeke101
      Helper II
      4 years ago

      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's avatar
        VahidDM
        Super User
        4 years ago

        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's avatar
    zeke101
    Helper II
    4 years ago

    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]))))