Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I have three calculated measures, Product Count which counts products, Dept Count which counts total products in a dept and Division Count which counts total products in the division. For example, Dept 1 has 5 different products with a total count of 35 which gives an average of 7 (35/7) and Division 1 has 15 different products with a total count of 60 which gives an average of 4 (60/15).
I want to calculate measures for average Dept count and Division count so that I can each product's product count against average Dept count and average Division count. Any help and guidance would be highly appreciated.
Solved! Go to Solution.
Proud to be a Super User!
Hi @Anonymous ,
Do ‘7’ and ‘4’ what you said above are part of the results of your measures ‘average Dept count’ and ‘average Division count’?
And are your three calculated measures created like so:
Product Count = COUNT ( 'Table 1'[Product Name] ) + 0 Dept Count = CALCULATE ( COUNT ( 'Table 1'[Product Name] ) + 0, ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] ) ) Division Count = CALCULATE ( COUNT ( 'Table 1'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] ) )
‘Table 1’ is the specific value table, for example, sales. And ‘Table 2’ is the specific product category table.
If so, you can create your measures ‘average Dept count’ and ‘average Division count’ like below:
Different product count of every Dept = CALCULATE ( DISTINCTCOUNT ( 'Table 2'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] ) ) Different product count of every Division = CALCULATE ( COUNT ( 'Table 2'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] ) ) average Dept count = DIVIDE ( [Dept Count], [Different product count of every Dept], 0 ) average Division count = DIVIDE([Division Count],[Different product count of every Division],0)
Best Regards,
Icey Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Do ‘7’ and ‘4’ what you said above are part of the results of your measures ‘average Dept count’ and ‘average Division count’?
And are your three calculated measures created like so:
Product Count = COUNT ( 'Table 1'[Product Name] ) + 0 Dept Count = CALCULATE ( COUNT ( 'Table 1'[Product Name] ) + 0, ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] ) ) Division Count = CALCULATE ( COUNT ( 'Table 1'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] ) )
‘Table 1’ is the specific value table, for example, sales. And ‘Table 2’ is the specific product category table.
If so, you can create your measures ‘average Dept count’ and ‘average Division count’ like below:
Different product count of every Dept = CALCULATE ( DISTINCTCOUNT ( 'Table 2'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] ) ) Different product count of every Division = CALCULATE ( COUNT ( 'Table 2'[Product Name] ), ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] ) ) average Dept count = DIVIDE ( [Dept Count], [Different product count of every Dept], 0 ) average Division count = DIVIDE([Division Count],[Different product count of every Division],0)
Best Regards,
Icey Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey and @Nathaniel_C , one more request, please!
How can I eliminate products from the distinct count in Dept and Division where product count is 0. For example, In Dept 1, DP 15 has a zero count, so distinct count of products for Dept 1 shall be 4 instead of 5.
I tried IF(Not(IsBlank(Product Count) but it does not give the correct answer.
Thanks,
Hi @Anonymous ,
You can change the measures like so:
Different product count of every Dept 2 = CALCULATE ( DISTINCTCOUNT ( 'Table 1'[Product Name] ) + 0, ALLEXCEPT ( 'Table 2', 'Table 2'[Dept Name] ) ) Different product count of every Division 2 = CALCULATE ( DISTINCTCOUNT ( 'Table 1'[Product Name] ) + 0, ALLEXCEPT ( 'Table 2', 'Table 2'[Division Name] ) ) average Dept count 2 = DIVIDE ( [Dept Count], [Different product count of every Dept 2], 0 ) average Division count 2 = DIVIDE ( [Division Count], [Different product count of every Division 2], 0 )
Here is my PBIX file. If you need, you can download it.
Best Regards,
Icey
Hi @Icey , thanks for ther response. The new measure still count Product Name with zero Product Count. In Dept 1, four products have Product Count >0 and I want to count them only in my distinct count in Different Product Count of every Dept. The logic that I want to build is to create a flag for active and inactive product, and count only active products.
Thanks again for your support.
Thanks, @Icey, and @Nathaniel_C for your time and support. I was looking for a solution similar to @Icey and it really helped.
Hi @Anonymous ,
If I understand what you are looking for, in the data shown, the average dept count would be (35+10+0+15)/4 or 15 and the average division count would be 60/1?
Nathaniel
Proud to be a Super User!
Proud to be a Super User!
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
79 | |
61 | |
60 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |