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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CloudMonkey
Post Prodigy
Post Prodigy

distinctcount only those that have an aggregate sum of at least 15

Hello,

 

The formula below find the number of shops that have sales in the latest 3 months. Please could you tell me how to only count shops that have at least 15 sales (sum of column 'Managagement Accounts Volume'[Volume]) in the latest 3 months?

 

Thank you for your help

 

Number of Large (15+ sales in latest 3 months) branches (active) = 
VAR FirstDayOfThisMonth = min('Calendar (Months)'[First Date Of Month])
VAR FirstDayOfNextMonth = edate(FirstDayOfThisMonth,1)
VAR FirstDayOfTwoMonthsAgo = edate(FirstDayOfThisMonth,-2)
RETURN
calculate(DISTINCTCOUNT('Management Accounts Volume'[Branch Number]),filter('Management Accounts Volume',
    related('Branch List (HARDCODED TO FY21) (excludes 9* accounts)'[Closed Date]) >= FirstDayOfNextMonth &&
    related('Branch List (HARDCODED TO FY21) (excludes 9* accounts)'[Acquired Date]) < FirstDayOfTwoMonthsAgo &&
    'Management Accounts Volume'[Volume Date] >= FirstDayOfTwoMonthsAgo &&
    'Management Accounts Volume'[Volume Date] < FirstDayOfNextMonth
))

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@CloudMonkey , your measure is not very clear, but you need a measure like below assuming sales is your measure

 

countx(values(Table[Shop]), If([sales] >15, [Shop], blank()) )

 

 

View solution in original post

2 REPLIES 2
CloudMonkey
Post Prodigy
Post Prodigy

Brilliant thank you @amitchandak 🙂

amitchandak
Super User
Super User

@CloudMonkey , your measure is not very clear, but you need a measure like below assuming sales is your measure

 

countx(values(Table[Shop]), If([sales] >15, [Shop], blank()) )

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.