Calculate Individual Client vs Client Sector vs All Clients

[Thomas-B-Hudson]

Hi,

I am hoping someone can help me with a measure I am looking to build.

I have a dashboard which has a slicer for client names.

I want to build a measure which looks at the Time to Submit and Time to Placement for the selected client.

I now want to build on this further to show how the select client from the slicer compares to the industry sector as well as all clients.

I have my client table which tells me what industry each client falls under

Customer ID	Customer Name	Industry
1	Company A	GOV
2	Company B	GOV
3	Company C	FIN
4	Company D	RETAIL
5	Company E	HOSP
6	Company F	GOV
7	Company G	FIN
8	Company H	HOSP

I then have another table which also has the company ID and I have built two measure's off to give me the time to submit and time to placement which when put together in a dashboard looks something like this:

Avg Time to Submit = 'Measure'[Total Time To Actioned (WD)] / 'Measure'[Count jobOrder JobFirst Placed],

Avg Time to Placement = 'Measure'[Total Time To Place First Job (WD)] / 'Measure'[Count jobOrder JobFirst Placed]

Customer ID	Customer Name	Industry	Time to Submit (measure)	Time to Placement (measure)
1	Company A	GOV	1.5	3.8
2	Company B	GOV	1.1	4.1
3	Company C	FIN	1.2	3.6
4	Company D	RETAIL	0.8	2.4
5	Company E	HOSP	1.7	3.2
6	Company F	GOV	1.7	3.6
7	Company G	FIN	1.4	3.9
8	Company H	HOSP	1.2	4

So when I have a slicer with the company name selected as A I want to be able to show the avg time to submit vs the industry of the selected client in the slicer vs all client, eg:

Company A = 1.5

Industry (GOV) = 1.43 (1.5 + 1.1  + 1.7) / 3 gov clients

All Client = 1.325

Any help is appreciated

[v-kkf-msft · Accepted solution]

Hi @Thomas-B-Hudson ,

Please try the following formula:

Avg_Industry = 
VAR tab =
    SUMMARIZE (
        ALL ( client ),
        client[Customer ID],
        client[Industry],
        "s", [Time to Submit]
    )
RETURN
    AVERAGEX ( FILTER ( tab, [Industry] = MAX ( client[Industry] ) ), [s] )

Avg_Total = 
AVERAGEX ( ALL ( client ), [Time to Submit] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Thomas-B-Hudson]

Thank you! This has worked!

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[sturlaws]

Hi,

the Industry average-measure can be written like this:

industry_average =
VAR _industry =
    CALCULATE ( SELECTEDVALUE ( 'DimCustomer'[Industry] ) )
RETURN
    AVERAGEX (
        FILTER ( ALL ( DimCustomer ), DimCustomer[Industry] = _industry ),
        [Avg Time to Submit]
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Thomas-B-Hudson]

Hi @sturlaws ,

Thanks for you help although I can't seem to get the measure to calculate correctly.

When I select a client I am able to see the measures specific to just the client and compare this to all clients, however the industry comparison is blank (See below):

When I have no client selected in my measure I can see that for the industry comparative the values remain the same 

Any idea's what might be causing this?

[sturlaws]

If you are in need of more assistance, you need to provide more information about your data, your model and your model. Preferably by creating a sample report which replicates your issues, and sharing that(upload to some cloud storage, and share the link)

How to Get Your Question Answered Quickly