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
tylerfutures
Frequent Visitor

Comparing district sales to total sales in a clustered column chart....problems

I am not entirely sure this is possible but here is what I am trying to do...

 

I want to create a column chart that shows 2 columns, district sales compared to total sales.  The axis is a series of products and the values is a 13 week sales average calculation.  Ideally I can create a slicer that someone can then select through each district to see the comparison of average sales to total average sales (to see if the district is below or above the sales average for all districts).

 

I feel like there is a simpler way to do this but I am overthinking it. Or not thinking about it enough.  I do not think a slicer can work on part of a graph (because I want the slicer to pass through districts to one column on the graph but not the total sales, that should stay static throughout the graph).

 

I have been messing with the Analytics tab to no avail.  I am open to any solution.  Any help is appreciated!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hey @tylerfutures

 

Thanks for the info, it helped replicate your situtation. I think I got it... hopefully I modeled your data correctly. Try this:

 

 

Measure = 
VAR CurrentSic = FIRSTNONBLANK(SIC[SIC_Code], 1)
RETURN
CALCULATE(
    AVERAGE(District[SalesAmount]),
    FILTER(ALL(District), RELATED(SIC[SIC_Code]) = CurrentSic)
)

 

 

I created 2 tables that look like this (District on the left, SIC on the right):

 

Table1.PNGTable2.PNG

 

 

With the measure above, you end up with a visualization that looks like:FinalGraph.PNG

Hopefully I didn't over-simplify your data model and this still holds up for you.

 

Let me know if it works!

Parker

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hey @tylerfutures

 

Maybe try to create a measure with the following formula:

 

TotalAverage = 
CALCULATE(
    AVERAGE(Test[Sales]),
    ALL(Test)
)

The ALL clause will remove the filters and use all of your data in your calculation. Give that a try and let me know how that works for you.

 

Thanks,

Parker

Thank you for the quick reply!  I sense this is so close to the final solution.  I am going to tinker with it.

 

I have a quick question for you, is there a way the measure can follow the axis as well? I've attached a photo:

 

Untitled.png

 

One thing I'll mention is the SIC code data on the axis is contained in a different data source...should I merge the two?  Then I can possibly use the measure you created to segment by SIC?

 

Thank you again for the help, this is amazing!

Anonymous
Not applicable

Hey @tylerfutures

 

I'm having some trouble figuring out how your data is set up. I bet you're good to keep your table set up the same as you have it, but could you post some pictures of your table set up? Maybe show the two tables in the data tab.

 

Thanks,

Parker

Yeah no problem.  And I truly appreciate the help Parker, I would not have even come close to stumbling upon your measure!

 

The main data I am grabbing is below.  I ommitted some of the sensitive information, I do not think it is harmful to reveal it but just to be sure!  You can see that I am getting the 13 week average and district office from this data set.

 

Untitled1.png

 

The second data set has a multitude of columns that are not relevant for this, only the SIC code.  Each retailer has a SIC code.  The data is joined by Retailer ID in both data sets, a one to one relationship.

 

Untitled3.png

 

The report I'm trying to build is showing the average of all retailers in a particular SIC code and averaging their 13 week sales average.  When applying a slicer someone can drill down to a particular district office.  

 

The second column in the report will show a total of all retailers (no district distinction) and their 13 week sales averages by SIC code.  From the main report screenshot I sent, I can tell that your measure is grabbing the average of everything, however it is not passing along the SIC codes on the axis to it.  I am guessing this is something to do within the measure...some kind of tweak that allows the measure to bypass the slicer but still calculate an average of all retailers within that particular SIC code?

 

Either way your help has been tremendous.  I feel like the measure is incredibly close.  Any input would be fantastic!

Anonymous
Not applicable

Hey @tylerfutures

 

Thanks for the info, it helped replicate your situtation. I think I got it... hopefully I modeled your data correctly. Try this:

 

 

Measure = 
VAR CurrentSic = FIRSTNONBLANK(SIC[SIC_Code], 1)
RETURN
CALCULATE(
    AVERAGE(District[SalesAmount]),
    FILTER(ALL(District), RELATED(SIC[SIC_Code]) = CurrentSic)
)

 

 

I created 2 tables that look like this (District on the left, SIC on the right):

 

Table1.PNGTable2.PNG

 

 

With the measure above, you end up with a visualization that looks like:FinalGraph.PNG

Hopefully I didn't over-simplify your data model and this still holds up for you.

 

Let me know if it works!

Parker

This looks like a winner!  That is brilliant work Parker!  Thank you so much!!!

Anonymous
Not applicable

Awesome @tylerfutures!

 

Glad this worked out! Could you make sure to mark the last response as the solution for anyone who finds this thread in the future?

 

Thanks,

Parker

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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