Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
))
Solved! Go to Solution.
@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()) )
@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()) )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |