DAX Function to filter by quarter and sum with measure

[RenanSSimao]

https://1drv.ms/u/s!AnPIqJrsY30EhFQSmQCrXuQy-U1B?e=oecpxg (FILES)

Hello everyone! I made a generic data based on my real data to try to explain to you my issue.
This is an example of my sheet. Each "Liderança" or "Leadersheep" has a goal to Visit 3 places per quarter. Each different "ID" represents a differente visist, so first of all I made a DistinctID = DISTINCTCOUNT(Sheet[ID]) to count how many visits were made by each Leadersheep/Engineer.

Sometimes, the engineer makes more visits than their goal per quarter (3 visits) so I made another measure to transform all those surplus to maximum 3  / #MaxMeasure = if([DistinctID]>=3,3,[DistinctID]).

So I have this simple visualition to show if the enginneers are achieving their goals per quarter:

The problem is: When I selected each Leadersheep the graphic works fine, showing the correct %, but, when I don't select any leadersheep the graphic shows every quarter with 100% because it sums every visist and that sunplus the goal which is 3

I'm having trouble separating the correct number for each leadersheep, because, if one leader makes 10 visits and the other makes 2, the graph shows that the goal is ok, since the sum is 12, but doesnt show that one leader made less visits than necessary.

I hope I was clear enough but any questions I can try to explain differently!

Thank you a lot

[lbendlin · Accepted solution]

*Measure Quarter =

var a=SUMMARIZE(Sheet,Sheet[Liderança],"visits",min(DISTINCTCOUNT(Sheet[ID]),3))

return divide(sumx(a,[visits]),COUNTROWS(a)*3)

see attached

[RenanSSimao]

@lbendlin , hello again!

If you don't mind answering, I was wondering if there is a way to show a card with the average of visits per quarter. For example:

Is there a way to use this "new data" from the new summarazed table and take the average of it per quarter?


Thank you a lot

[lbendlin]

the average of visits per quarter

that would require a single card. However your description 

seems to expect four different cards, each for the YTD of the respective quarter.  How would that work in the UI?

[RenanSSimao]

Yes, sorry. I thought one thing and wrote another, you're right.

I mean a single card that shows the average of that result showing on the graph for the entire year, in that case, if I would be able to sum that total showing for each column in the graph and divide it by 4 (total of quarters).

[lbendlin]

Add the measure to the card, aggregate as average.

[RenanSSimao]

Hello Ibendlin!

I don't understand how to aggregate a measure as average in Power Bi, since I can only select the aggregate options when choosing a column, not a measure.

Thank you

[lbendlin]

you do that in the measure definition.

[RenanSSimao]

Hello @lbendlin !

Thank you very much, that works perfectly.

Thanks!