Divide sum of one column by sum of another column

[ansa_naz]

Hi all

I am hoping someone can help with this measure, I have tried DISTINCTCOUNT with this in lots of ways but I cant seem to get what I need. I have the following two tables:

Table - Reports

ID             LiftID

1              122

2              256

3              122

4              665

5              665

6              788

7              788

8              788

9              256

Table - Lifts

LiftID          EngID

122            CR01

256            NA01

665            CR01

788            ST02

687            NA01

688            NA01

I want to be able to count how many reports each engineer EngID has, and divide by a count of how many lifts each engineer (EngID) has. There are multiple filters on the screen too, so any filters applied would also be taken into account when calculating the counts and the measure

So for the above tables, I have the following counts:

Reports

EngID       Count

CR01        4

NA01       2

ST02        3

Lifts

EngID        Count

CR01         2

NA01        3

ST02         1

And the measure should return:

Reports.EngCount / Lifts.EngCount

CR01        2             (this is calculated as 4 / 2)

NA01       0.666       (this is calculated as 2 / 3)

ST02        3               (this is calculated as 3 / 1)

I just cannot figure out how to do this at all, so any help would be most appreciated

Cheers

[affan]

Hi @ansa_naz

You can see the attached PBIX file here .

I have got the following result by addeing the measures.

Required = Var countR =CALCULATE(COUNTROWS(Reports),ALLEXCEPT(Lifts,Lifts[EngID]))
Var countl=CALCULATE(COUNTROWS(Lifts),ALLEXCEPT(Lifts,Lifts[EngID]))
Return
countR/countl

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[rajulshah · Accepted solution]

Hello @ansa_naz,

Please use the following measure:

Measure = DIVIDE(COUNT(Reports[LiftID]),COUNT(Lifts[LiftID]))

Hope this helps.

[ansa_naz]

Brilliant, this worked perfect! Thanks @rajulshah!

[Anonymous]

Hi @ansa_naz

Create Relationship between these two tables and write the below DAX to create a measure.

          TotalCount = SUM(Reports[Count])/SUM(Lifts[Count])

For more details see the pbix file.

Download pbix file

[ansa_naz]

Hi @Anonymous this doesnt work as the column 'Count' does not exist. I am trying to write a DAX measure which uses Count within it, it is not already an existing column