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.
I have this code that shows me the disctinctcount of stores with any sales. I'm using this in a calendar visual. How would I get the average distinct count at each calendar category. In the visual on the bottom i want the monthly to show 19 stores instead of 39.
Stores =
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)
Solved! Go to Solution.
Hi @tqn626 ,
You can try the following measure:
Stores =
IF (
ISINSCOPE ( the_hierarchy_in_fw_quarater ),
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
),
AVERAGEX (
DimStore_List,
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)
)
)
If you need accurate formula, please show us sample data.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@tqn626 , Based on the group level of Day or month then create a measure like
Stores =
var _1 =
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)
return
sumx(values(Date[Date]),_1)
Hmm, This didn't work, I got a much larger number.
Hi @tqn626 ,
You can try the following measure:
Stores =
IF (
ISINSCOPE ( the_hierarchy_in_fw_quarater ),
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
),
AVERAGEX (
DimStore_List,
CALCULATE (
DISTINCTCOUNT ( DimStore_List[Store] ),
FILTER ( 'Daily Data', 'Daily Data'[Net_Sales] > 0 )
)
)
)
If you need accurate formula, please show us sample data.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |