Count of ID compared to Average

[Caitlin_Knox]

I am trying to solve what seems like a simple calculation but can't quite figure it out. I've successfully modeled out what I'd like in Excel. Essentially, I want a table that shows the Sales Support person's name, the count of ID's, and then the total average of ID's, with a fourth column that shows the difference between the average and their count.

 

[Hasan]

HI,

1-create a new colunn and use this formula Average=Average(m_Count)

2-create another columb and use this formula Differerence=m_count-Average

[Caitlin_Knox]

There is not column like Column2 in your example.

The column that I'm counting is the ID field. So I'm counting how many ID's they have assigned to them, which shows as a sum in the table visualization. I think the solution would be something with a CALCULATE that would filter on the Assigned Support Person's name, but that's where I'm struggling. The actual ID is a 4 digit number, so each time I try and use the AVERAGE function, its giving me the average of that number, instead of the Count. And, I can't do the Average of the count- since AVERAGE requires a column reference.

In Excel, I just had to reference the count column that had the sum of id's. I just don't know how to reproduce this action in DAX

[Hasan]

I got it. How about using grouping options in query editor mode so you can have count of IDs as new column.

[Caitlin_Knox]

I've never used this technique before. Where do I begin?

[Hasan]

I can work on it if can share the sampe row data fro me or you can check out his link

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-common-query-tasks/#group-rows

[Caitlin_Knox]

Thanks for your reply, I got as far as copying the query, then grouping to get the totals. How do I keep the ID field as a separate column so I can create the necessary relationships since this is a separate table now?

[Hasan]

Can you group by ID so it will be easy build the relationship?

[Caitlin_Knox]

I was able to relate it to my Data table, but in order to look at it by month, I need to also relate it to my Date table which is not possible becasue once I group by the Name it removes the datekey column in the orgiinal query

[v-jiascu-msft]

Hi @Caitlin_Knox,

Maybe you could try this formula.

Average =
AVERAGEX (
    SUMMARIZE (
        'Quotes Odata',
        'Quotes Odata'[AssignedSupportPerson.Name],
        "m_count", COUNT ( 'Quotes Odata'[ID] )
    ),
    [m_count]
)

Best Regards!

Dale

[Caitlin_Knox]

I have tried that logic of formula, as I found in this post https://community.powerbi.com/t5/Desktop/Average-count-per-month-per-id/m-p/212988#M94068

However, I'm still not achieving the desired result. Maybe something else, other than the formula is inaccurate.

I'm adding the Average formula as a measure. I then created a Difference measure that subtracts the count from the average. This is the column I want to rank on- to simply see how far away a person is from the average. I hope you can see in the screen shot, the issues with the data.

In my head, I feel like the Average column should actually be the same for each person- I don't understand how ths is different for each person. The difference would be the number that is different.  When a date designation is made, the average numbershould change.

[v-jiascu-msft]

Hi @Caitlin_Knox,

Yeah, maybe you could try this. Could you please post a sample in the text mode?

Average =
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            'Quotes Odata',
            'Quotes Odata'[AssignedSupportPerson.Name],
            "m_count", COUNT ( 'Quotes Odata'[ID] )
        ),
        [m_count]
    ),
    ALL ( 'quotes odata' )
)

Best Regards!

Dale

[Caitlin_Knox]

no, becasue that just makes the count of each ID 1

[Caitlin_Knox]

m_Count is a measure that Coutns the ID's. Its not letting me reference that in the new column.

m_Count = DISTINCTCOUNT('Quotes Odata'[Id])