Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
I am trying to write a measure, which would calculate the Number Of Reports/Number of Lifts. So I have created the following measure::
Items per Lift = divide(count('Insurance Matrix'[Report Item]),(COUNT((ANSAPBILifts[SiteIDLine]))))
The problem with the above measure, is that it needs to ignore all filters on the denominator side (the right hand side), but it doesnt. So I then tried the below measure, as I read on these forums that I need to include an ALL statement at the start of the denominator side:
Items per Lift = divide(count('Insurance Matrix'[Report Item]),(COUNT((ANSAPBILifts[SiteIDLine]))))
However, this time it doesnt work at all. I am not sure where to go from here, so if anyone can guide me in the right direction it would be very appreciated!
The data is organised, in a basic sense, as follows:
ANSAPBILifts table
SiteIDLine Engineer
123 Jon
124 Craig
125 Jon
Insurance Matrix table
Report Item SiteIDLine
1 123
2 125
3 125
4 123
5 123
6 124
I have various filters on the page which can be applied to the Insurance Matrix table. However, when applying these filters, I dont want them to affect the Count of items from the ANSAPBILifts table. Is this possible?
Cheers for all help
Solved! Go to Solution.
hi, @ansa_naz
You can try to this formula as below:
Items per LiftALL = divide(count('Insurance Matrix'[Report Item]),CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ])))
and add use this measure instead of Count of Engineer ID
Measure = CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ]))
Result:
When I don't select anything in slicer
and then select some condition
Best Regards,
Lin
Hi @ansa_naz
You are not far off.
This should work
Items per Lift =
divide(
count('Insurance Matrix'[Report Item]),
CALCULATE(
COUNT(ANSAPBILifts[SiteIDLine]),
ALL(ANSAPBILifts)
)
)
ALL(Count..) is incorrect because the ALL function work on a table or column.
CALCULATE allows you to make a calculation on the first argument and change the filter context on the second argument
Hi @Arentir thanks for this reply. This nearly works too, however, the denominator is using a count of ALL lifts (which does not change with filters), but it needs to be a count of all lifts per engineer (which does not change with filters) - any ideas on how I can do that?
So for example, ANSAPBILifts table has a total of 8106 rows, and the count per engineer within this table is for example:
Jon - 250
James - 125
Chris - 555
So what I am trying to do is ensure that the denominator is always the count of the engineer in the chart, not 8106. eg for Jon, it is always 250, no matter what other filters are applied on this page. For James it is always 125, etc
Hope that makes sense!
Cheers
Change the ALL section to ALLEXCEPT and plug in the column that lists the engineers, so it'll use that filter context but nothing else
Hi @jthomson thanks for that reply, I have done as you suggested:
Items per LiftALL = divide(count('Insurance Matrix'[Report Item]),CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Route Eng])))
The measure is now correct as long as no filters are applied on the page. However, as soon as a filter gets applied, the denominator value gets filtered too
See below data from the chart where it is being used:
So the first image above - for engineer MRob, the measure should be 559/99 = 5.6464. This is correct
The second image - for engineer MRob, the measure should be 36/99 = 0.3636. However, th measure is using a filtered denominator value of 26, so the measure is calculated as 36/26=1.3846
Any ideas on how I can get the denominator in the division to be fixed per RouteEng?
Cheers
hi, @ansa_naz
You could use ALLEXCEPT Function in your formula, whether there are other conditional?
Could you share your sample pbix file and the expected output? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi @v-lili6-msft i have sent you a private message with a link to the PBIX file. Many thanks for your help!
Hi @v-lili6-msft see below sample PBIX file:
https://1drv.ms/u/s!AuiIgc_S9J5JhbM3cwRUmx21FuUIUQ
Cheers
hi, @ansa_naz
You can try to this formula as below:
Items per LiftALL = divide(count('Insurance Matrix'[Report Item]),CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ])))
and add use this measure instead of Count of Engineer ID
Measure = CALCULATE(COUNT(ANSAPBILifts[SiteIDLine]),ALLEXCEPT(ANSAPBILifts,ANSAPBILifts[Engineer ID]),ALL('Insurance Matrix'[Status],'Insurance Matrix'[Technical Markup ]))
Result:
When I don't select anything in slicer
and then select some condition
Best Regards,
Lin