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

Measure that averages District Office and Total Company sales per week

I have appended a table to include sales for Salespeople.  Each Salesperson belongs to a District Office.  I have included a slicer that slicers first by District Office and then Salesperson

 

I would like to create 2 additional measures to include in a clustered column chart: one that averages all sales in each District Office and then one that averages all sales for the entire office (everyone, I guess you could say average all sales per District Office or all sales of every salesperson).

 

I would like these 2 measures to be independent of the slicers...so when someone selects "New York" and "Stan Smith" for instance they would see a sales bar for Stan Smith as well as the New York office and then the entire firm.

 

The final wrinkle...The axis on my chart is by weekending date.  So ideally the 2 additional measures would average all sales in that particular week.  THe idea is so that sales staff can see their performance for the week compared to the DO as well as the company.

 

This really neat measure was created that gets me 80% there.  It shows a static average and does not allow for a calculation for each week.  Any help is appreciated!!!

 

All District SIC Average = VAR CurrentSic = FIRSTNONBLANK(DO and DSR[District Office], 1)

RETURN

CALCULATE(

    AVERAGE('2017-2018'[Settlement Sales]),

    FILTER(ALL('DO Sales'), RELATED(DO and DSR[District Office]) = CurrentSic))

 

 

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@tylerfutures,

 

The requirement is not very clear. You may try to use ALLSELECTED Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tylerfutures
Frequent Visitor

Sorry, I made a mistake: the District Office measure WOULD follow the slicer (if "New York" is selected the measure would then return sales averages for the New York office).  

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.

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.

Top Solution Authors