Applying Multiple Filters To Dynamic Bin Chart Using Mean and Sum

[Anonymous]

I am currently trying to create a dynamic graph in power bi that is giving me a bit of trouble.  I currently have a data set with 4 columns - one is a value, and three are categorical.  Let's call them columns V, C1, C2, and C3.  The format of the data is as follows, with example dummy values:

Value (V)	Category 1 (C1)	Category 2 (C2)	Category 3 (C3)
10	1	1	1
10	1	1	2
30	1	1	3
10	1	2	1
20	1	2	2
30	1	2	3
30	1	3	1
10	1	3	2
10	1	3	3
30	2	1	1
20	2	1	2
10	2	1	3
20	2	2	1
20	2	2	2
10	2	2	3
20	2	3	1
30	2	3	2
30	2	3	3

My eventual goal is to create a graph with two slicers - one for C2, and one for C3.  The slicers will trim the data set used in calculation to only include rows with the values selected by C2 and C3, and can contain any subset of unique category variables that occur in each column respectively.  On this graph, there will be one data point for each unique category value in C1.  The graph will be a bin chart, and each bin will have a value in it if the values encompassed by the set of values in V that match each unique category value in C1 fall into that amount after going through a series of calculations.

I know how to set up slicers, and how to bin data, but dynamically updating the table to do the proper bin calculations based on what is selected in the slicers is the part I am having trouble with.  I will walk through step by step how the calculations should work with the example dummy data above.  First, lets assume that we select to only include categories 1 and 2 for C2, and only categories 1 and 2 for C3.  That will then make the data that we are working with look like this:

Value (V)	Category 1 (C1)	Category 2 (C2)	Category 3 (C3)
10	1	1	1
10	1	1	2
10	1	2	1
20	1	2	2
30	2	1	1
20	2	1	2
20	2	2	1
20	2	2	2

After this I would like to "compress" the table by effectively eliminating C3, then combining each row that contains the same values for both C1 and C2.  When combining these rows, I want this to average the values that are combined together.  After this step is done, the data will look like this for our example:

Value (V)	Category 1 (C1)	Category 2 (C2)
10	1	1
15	1	2
25	2	1
20	2	2

After this, I would then like to "compress" the table again by effectively eliminating C2 and combining rows that have the same values for C1, just like the previous step, but this time summing the values in the V column that get combined together.  The resulting data in our example will look like this:

Value (V)	Category 1 (C1)
25	1
45	2

Finally, I would like to take each final calculated value in V that matches each unique category value in C1, and bin those.  For our example, lets say we have bins for values from 1-60 with a bin size of 10.  That would leave our final bin chart looking like this for our example:

Count
2
1			||||||||||		||||||||||
Value	1-10	11-20	21-30	31-40	41-50	51-60

If just calculating this one time, I could just make a set of queries to continuously trim the data down to what I want, then display it in the bin chart.  However, the difficult part is that I need to recalculate this and remake the graph each time the categories we are using in C2 and C3 are changed by the slicers.  So I assume I would probably need to do this in DAX, but after studying that for a while, I am still not sure how to do it there.  If anyone could provide a way for building this sort of graph in Power BI, or let me know if it is not currently supported, I would really appreciate that!  Thank you so much!

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

First you need to create a measure as below:

Measure = SUMX(VALUES('Table'[Category 2 (C2)]),CALCULATE(AVERAGE('Table'[Value (V)])))

Then create a bin table as below：

Then create a measure as below:

Measure 2 = var a = MAX('Table (2)'[Count])
var b =  CALCULATE([Measure],FILTER(ALLSELECTED('Table'),'Table'[Category 1 (C1)]= a))
var mid= SEARCH("-",MAX('Table (2)'[value]))
var mind = LEFT(MAX('Table (2)'[value]),mid-1)+0
var maxd = RIGHT(MAX('Table (2)'[value]),LEN(MAX('Table (2)'[value]))-mid)+0
return
IF(b>=mind && b<maxd,1,BLANK())

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you very much - this is quite useful!  The bit on displaying the bins in the table is a little bit buggy when switched over to my original data set instead of just the testing one, but I should be able to work out those out on my own.  You really helped me a lot!