Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Applying Multiple Filters To Dynamic Bin Chart Using Mean and Sum

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 th...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

     

     

     

     

v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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!