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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jbaker15
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
jbaker15
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.
 

 

 
 

 

 

 

v-alq-msft
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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.