Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DMSJennifer
Helper I
Helper I

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.