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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MarkCBB
Helper V
Helper V

DAX Count of Stores that are under the Average

Hi there,

 

I am struggleing with a DAX Measure, I would like to count the number of Stores that are under the average sales amount.

I am the following supporting DAX Measures.

 

This Year To Date Units

TYTD Units = TOTALYTD(CALCULATE([Total Units]),'CALENDAR'[DATE])

Number of Unique Stores/Sites

Site Count = CALCULATE(DISTINCTCOUNT(MASTER_SITE[STORE NAME]))

The average Unit Sales (This Year To Date)

TYTD Store Average (U) = DIVIDE([TYTD Units],[Site Count],0)

 

So the result I am looking for is the distinct count of Stores/Sites that are under the average Unit sales. 

 

 

 

3 REPLIES 3
tringuyenminh92
Memorable Member
Memorable Member

Hi @MarkCBB

 

Your concern is really really interestingSmiley Very Happy. I will create one Dates table and 3 calculated measure:

  • This Year to date Units

 

TYTD Units = CALCULATE(SUM(Trans[Unit]),filter(all(Dates[Date]),Dates[Date] <= MAX(Dates[Date]) ))

1.png

 

 

  • The average unit Sales (This Year To Date)

 

TYTD Store Average (U) = CALCULATE(DIVIDE([TYTD Units],DISTINCTCOUNT(Trans[Store])),ALLEXCEPT(Trans,Dates[Date]))

Re-use YTD units and using ALLEXCEPT(Date) to ensure that same date will have same average sales value. Please take a look top right table: 

 

2.png

 

  • The rest of things is count store/site under average:

 

No. of Store/Sites under AVG = CALCULATE( DISTINCTCOUNT(Trans[Store]),filter(Trans,[TYTD Units] < [TYTD Store Average (U)] ))

 

Sample and data: https://lookaside.fbsbx.com/file/Sales_lessthanAVG.zip?token=AWwFcKFLfLdjsS9Vg71blqVZGYcZgYY6y951-oo...

 

 

If this works for you please accept it as solution and also like to give KUDOS.

 

Hello @tringuyenminh92

 

Thank you for your detailed responce, I have been able to implement this, however the solution you have given is very heavy on RAM. I believe that it is the FILTER() that is using so much, my fact table is just over 10million rows. I have 64g RAM (1600MHZ). And I used 100%. I have to use filters (i.e. Region.Proviance) to get the result to calculate.

 

Can you think of another way to do this that does not use FILTER. Or maybe a New table to cut down the number of FILTERS that run.

 

PS, I remove the Date MAX, from the Measure as I am already using the TOTALYTD, with a Calendar Table.

 

I look forward to your responce. 

 

Regards,

MarkCBB.

 

Hi @MarkCBB,

 

I will say Yes, we could consider and prefer using Calculated Column than Calculated Measure (to cut down memory computing), but when using Calculated Column, we need to handle row context and this will 2 two risks as my perspective:

  • if we have some filter by another Dims like times, stores or region/province,... (even though we could use allexcept, allselected for filters situation)
  • if we support multiple select in slicers, we might not handle it by DAX

 

So i will replace measure TYTD Units by calculated column:

 

TYTD Units - By Calculated Column = CALCULATE(SUM(Trans[Unit]),filter(ALLEXCEPT(Trans,Trans[Store]),Trans[Date] <= EARLIER('Trans'[Date])))

 

 

and use it in TYTD Store Average (U) :

 

TYTD Store Average (U) - using TYTD column = CALCULATE(DIVIDE(sum(Trans[TYTD Units - By Calculated Column]),DISTINCTCOUNT(Trans[Store])),ALLEXCEPT(Trans,Dates[Date]))

 

 

You could try to make "TYTD Store Average (U)" or "No. of stores under average" by calculated column, but i know you will show them in lots of charts and tables with slicers so there are a lot of things need to be done when doing that. it's really complicated now 😞

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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