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

Creating a Histrogram that changes based on user filters

I have an interesting problem that I have spent more hours than i'd like to admit trying to figure out. I have the below data set example:

 

1) wm_week is the year/week of the data point.

2) jj_sales_director is the director category. There are 3 of these.

3) jj_sales_category is a category level. There are 20 or so different categories.

4) store_nbr is the store number.

5) ris is a % up to 100%.

 

jbaker15_0-1593099623132.png

I have created a histogram using a stacked column chart below:

jbaker15_1-1593099891569.png

 

I am trying to count the distinct stores by average RIS in the above buckets, which appears to work in the above. The problem is if I filter to the category or director I want it to the change the above visual to bucket the average RIS for that specific filter combination. RIght now it just doesnt change the counts when the user filters the dashboard.

 

I have made a few calculated columns below that are probably the issue but I have been unable to find a way to do it:

 

Averages the instock and filters by just store: (Calculated Column)

Avg inStock =
VAR store1 = 'Instock L30 Days'[store_nbr]
RETURN
CALCULATE (
AVERAGE ( 'Instock L30 Days'[ris]),
ALL ( 'Instock L30 Days' ),
FILTER ( 'Instock L30 Days', 'Instock L30 Days'[store_nbr] = store1)
)

 

Creates the buckets: (Measure)

Range1 = var temp= AVERAGE('Instock L30 Days'[Avg inStock]) return
if(temp<=10,"0-10%",if(AND(temp>10,temp<=20),"10-20%",if(AND(temp>20,temp<=30),"20-30%",if(AND(temp>30,temp<=40),"30-40%",if(AND(temp>40,temp<=50),"40-50%",if(AND(temp>50,temp<=60),"50-60%",if(AND(temp>60,temp<=70),"60-70%",if(AND(temp>70,temp<=80),"70-80%",if(AND(temp>80,temp<=90),"80-90%",if(AND(temp>90,temp<=92),"90-92%",if(AND(temp>92,temp<=94),"92-94%",if(AND(temp>94,temp<=96),"94-96%",if(AND(temp>96,temp<=98),"96-98%","98-100%")))))))))))))
 
Creates the column to use in the visual: (Calculated Column)
Column = 'Instock L30 Days'[Range1]

 

Below is how I have placed the data in the column chart.

jbaker15_2-1593100150155.png

Any help would be appreciated.

 

 

 

3 REPLIES 3
Regular Visitor

Since calculated columns do not work through filters in real time as you suggest, I tried a different approach but still no luck. This is incredibly frustrating because I am sure there is a simple answer.

 

I created a Bucket Table to break it down into the groups I want on the Histogram:

jbaker15_0-1593636371588.png

I then created a measure to put the data into the bins:

 

Bins = CALCULATE(DISTINCTCOUNT('Instock L30 Days'[store_nbr]),(FILTER('Instock L30 Days',and(average('Instock L30 Days'[ris]) >= min(Buckets[Min]),average('Instock L30 Days'[ris]) <= MAX(Buckets[Max])))))
 
The trouble with this method, is it averages everything together into one bucket and does not break the stores out into each bucket.
jbaker15_0-1593636780738.png

 

I need it to average each store and THEN distinct count it into the buckets based on whatever filters are active at the time.
 
Still lost on this one.
 

 

 
 

 

 

 

Community Support
Community Support

Hi, @jbaker15 

 

I'd like to suggest you modify the calculated column as measure. A measure will reflect the selection of the slicer in time, and the column is refreshed only after loading and clicking the Refresh button, and it cannot be timely interacted with other visual interact.

 

Please try to use the following measures.

Avg inStock =
VAR store1 =
    SELECTEDVALUE ( 'Instock L30 Days'[store_nbr] )
RETURN
    CALCULATE (
        AVERAGE ( 'Instock L30 Days'[ris] ),
        FILTER (
            ALLSELECTED ( 'Instock L30 Days' ),
            'Instock L30 Days'[store_nbr] = store1
        )
    )

Range1 = var temp= [Avg inStock] return
if(temp<=10,"0-10%",if(AND(temp>10,temp<=20),"10-20%",if(AND(temp>20,temp<=30),"20-30%",if(AND(temp>30,temp<=40),"30-40%",if(AND(temp>40,temp<=50),"40-50%",if(AND(temp>50,temp<=60),"50-60%",if(AND(temp>60,temp<=70),"60-70%",if(AND(temp>70,temp<=80),"70-80%",if(AND(temp>80,temp<=90),"80-90%",if(AND(temp>90,temp<=92),"90-92%",if(AND(temp>92,temp<=94),"92-94%",if(AND(temp>94,temp<=96),"94-96%",if(AND(temp>96,temp<=98),"96-98%","98-100%")))))))))))))

 

Best Regards

Allan

 

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

 

Hello, I appreciate the repsonse. When I change Avg inStock to a measure I can no longer put it as a variable in Range1. It also wont allow me to put a measure in as a shared axis on a stacked column chart.

 

Maybe this isnt possible. Any other thoughts?

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors