cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DMSJennifer
Frequent Visitor

Benchmark grouping

Hey,

 

I hope someone can help me with the following problem: I want to make a dashboard with 2 tables: one table with individual data, selected by a slycer. And one table that (automatically) shows the same kind of data, but not with one individual customer, but with an average of multiple customers. Those customers have to be similar to the individual customer so you can show a benchmark.

 

For example: we want to benchmark on Total sales, and select all the customers with similar sales, and then show what the benchmark results are for average cost, average expences, average amount of products bought, etc.

 

I cant quite figure this problem out, so I hope some of you can help me with it!

Thanks in advance!

 

Kind greetings,

Jennifer

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@DMSJennifer , based on what I got, try measure like these for benchmark

 


calculate(averageX(values(Customer[Customer]), calculate(sum(Table[Amount]))), allselected(customer))

 

or

 

 

calculate(averageX(values(Customer[Customer]), calculate(sum(Table[Amount]))), all(customer))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

7 REPLIES 7
v-xulin-mstf
Community Support
Community Support

Hi @DMSJennifer

 

Is your issue solved?

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

v-xulin-mstf
Community Support
Community Support

Hi @DMSJennifer,

 

Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hey Link,

I can't provide sample data, but I can provide Printscreans if that could be helpfull?

Kind Regards,
Jennifer

Hi @DMSJennifer,

 

Okay!

Please describe your data structure and expected output.
Hope to help you as much as possible!

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

 

I have multiple tables in my dataset. Related to eachother via customer ID. Every table contains more than 20 collumns of data, we want to visualise in multiple ways and multiple forms. A lot of data is a result of data, provided by our customers. We advice those customers according to their data, compared to general data, created out of our dataset. For example: Customer A has a Total Sales of 56, a Total Amount Purchased of 3 and a General Budget of 70. I want to create a benchmark based on Total Sales. So I want to see what those customers with similar Total Sales, score on Total Amount Purchased and General Budget. Making clear how well or bad this Customer A scores compared to the benchmarkgroup. 
The Total Sales Benchmark groups are based on variable data, coming from a not-related table, so I can't hardcode the group on limit values. 
For example: 
Group         TotalSalesMin      TotalSalesMax
1                  0                            13

2                  13                          25

3                  25                          40

4                  40                          1000

 

Making 4 benchmarkgroups. 

 

Now my main question is: How can I approach this in the best way? 

I hope this cleares things up a little

 

Kind Regards,

Jennifer

Hi @DMSJennifer

 

Sorry for delay reply.

Don't the fact table and the base table have related columns? How does that correspond to the different customer ID?

You need a common column and then calculate the result according to the calculation logic of the benchmark.

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

amitchandak
Super User
Super User

@DMSJennifer , based on what I got, try measure like these for benchmark

 


calculate(averageX(values(Customer[Customer]), calculate(sum(Table[Amount]))), allselected(customer))

 

or

 

 

calculate(averageX(values(Customer[Customer]), calculate(sum(Table[Amount]))), all(customer))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!