Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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()) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |