Benchmark grouping

[DMSJennifer]

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

[v-xulin-mstf]

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]

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.

[DMSJennifer]

Hey Link,

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

Kind Regards,
Jennifer

[v-xulin-mstf]

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!

[DMSJennifer]

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

[v-xulin-mstf]

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 · Accepted solution]

@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))