Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate Individual Client vs Client Sector vs All Clients

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 IDCustomer NameIndustry
1Company AGOV
2Company BGOV
3Company CFIN
4Company DRETAIL
5Company EHOSP
6Company FGOV
7Company GFIN
8Company HHOSP

 

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 IDCustomer NameIndustryTime to Submit (measure)Time to Placement (measure)
1Company AGOV1.53.8
2Company BGOV1.14.1
3Company CFIN1.23.6
4Company DRETAIL0.82.4
5Company EHOSP1.73.2
6Company FGOV1.73.6
7Company GFIN1.43.9
8Company HHOSP1.24

 

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

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    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.

6 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        4 years ago

        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

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you! This has worked!