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.
Hi Everyone,
Background:
See screenshot below of the sample dataset i have (only for one store and one sales manager)
What I want to achieve:
I d like to show the development (per month) of the performance of the stores per sales managers per store categorie on a deep dive level (per store) and aggregated level. Therefore I d like to do the cumulative distinct count of stores, taking into account the store categorie, sales managers and month
See screenshots below of the tables I d like to get:
What I have:
I have created a measure that calculated the cumulative distinct count of stores and get filters per store catgeorie, sales manager & month (see DAX code below). However as soon as a store change categorie, it continues counting the store for the old categorie and start counting for the new categorie
# cumulative stores = VAR results = CALCULATE( DISTINCTCOUNT('Reports'[store id]), FILTER(ALLSELECTED(Date),date[Date]<= MAX(date[Date]))) RETURN results
See screenshot below of the table i get using the above measure:
Please let me know if you have a solution :),
Best,
Kevin
Solved! Go to Solution.
@Kevin_Conseil
If you can create a short sample and put it into pbix, it would be clear for me. For now, you can try add additional filters in the measure, because you are looking to count and filter by the salesman and categories.
result = CALCULATE( DISTINCTCOUNT('Reports'[store id]), FILTER(ALLSELECTED(Date),[Salesman]=max([Salesman]) && [categories]=max([categories]) && date[Date]<= MAX(date[Date])))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Kevin_Conseil
If you can create a short sample and put it into pbix, it would be clear for me. For now, you can try add additional filters in the measure, because you are looking to count and filter by the salesman and categories.
result = CALCULATE( DISTINCTCOUNT('Reports'[store id]), FILTER(ALLSELECTED(Date),[Salesman]=max([Salesman]) && [categories]=max([categories]) && date[Date]<= MAX(date[Date])))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |