Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
I made the following measures and would like to Total the Count Measure by Manager but it is not working.
Here's the two measures :
Measure 1:
Brands Vol Sales minus Category Vol Sales =
SUM('NB Share'[Brands Vol Sales]) - SUM('NB Share'[Category Vol Sales])
Measure 2:
# of Accts growing faster = COUNTX
(
FILTER
(
VALUES
( 'NB Share'[Geography]),
[Brands Vol Sales minus Category Vol Sales]
>
0),'NB Share'[Geography])
Hi! I am trying now to make a matrix table like the one below and rank the following brands by their volume sales in their category, but the ranking isn't working. I would want to filter by geography, time, and select the brands I would like to see (as you can see in the screenshot). Also it is using the same sample data I provided last time, except the Red Apple and Purple Grape = Our Brands.
For example, the Our Brand Apple I would like the ranking to be #1, since compared to the Green Apple they have higher volume sales.
Assuming your measure 1 is doing calc correctly, try this and see if works
Measure 2:
# of Accts growing faster =
var _m1Value = CALCULATE ( [Brands Vol Sales minus Category Vol Sales])
RETURN COUNTAX(
-- For all the values in the Geography
VALUES(Table1['NB Share'[Geography]),
-- Check if the value is > 0, then 1 else dont count
if( _m1Value > 0, 1, BLANK() )
)
Thank you for the reply! That didn't work but what I ended up doing was make Measure 2 a calculated column, and then did a COUNTROWS measure and that worked.
The code I provided is for measure and interesting why it did not work.
No worries, you got working as a calculated column. 🙂
and then the manager sample table is below, it is connected on Geography of the other table
Manager | Geography |
Joe | North |
Joe | West |
Harry | South |
Harry | East |
Sample data below: In red are the two measure I made and in blue is what I would like.
Geography | Time | Product | Category | Entire Category | %Vol Sales Change | Brands Vol Sales | Category Vol Sales | Difference between Brand and Overall | Count if more than 0 | TOTAL |
North | Last 4 Weeks | Group Apple | Apple | 10% | 10% | 2 | ||||
North | Last 4 Weeks | Product Red Apple | Apple | 12% | 12% | 2% | 1 | |||
North | Last 4 Weeks | Product Green Apple | Apple | 15% | ||||||
North | Last 4 Weeks | Group Grape | Grape | 20% | 20% | |||||
North | Last 4 Weeks | Product Purple Grape | Grape | 22% | 22% | 2% | 1 | |||
North | Last 4 Weeks | Product Green Grape | Grape | 18% | ||||||
North | Last 13 Weeks | Group Apple | Apple | 15% | 15% | 2 | ||||
North | Last 13 Weeks | Product Red Apple | Apple | 18% | 18% | 3 | 1 | |||
North | Last 13 Weeks | Product Green Apple | Apple | 13% | ||||||
North | Last 13 Weeks | Group Grape | Grape | 21% | 21% | |||||
North | Last 13 Weeks | Product Purple Grape | Grape | 24% | 24% | 3 | 1 | |||
North | Last 13 Weeks | Product Green Grape | Grape | 19% | ||||||
South | Last 4 Weeks | Group Apple | Apple | 8% | 8% | |||||
South | Last 4 Weeks | Product Red Apple | Apple | 5% | 5% | -3 | ||||
South | Last 4 Weeks | Product Green Apple | Apple | 12% | ||||||
South | Last 4 Weeks | Group Grape | Grape | 18% | 18% | |||||
South | Last 4 Weeks | Product Purple Grape | Grape | 26% | 26% | -8% | ||||
South | Last 4 Weeks | Product Green Grape | Grape | 20% | ||||||
South | Last 13 Weeks | Group Apple | Apple | 15% | 15% | 2 | ||||
South | Last 13 Weeks | Product Red Apple | Apple | 16% | 16% | 1% | 1 | |||
South | Last 13 Weeks | Product Green Apple | Apple | 11% | ||||||
South | Last 13 Weeks | Group Grape | Grape | 25% | 25% | |||||
South | Last 13 Weeks | Product Purple Grape | Grape | 26% | 26% | 1% | 1 | |||
South | Last 13 Weeks | Product Green Grape | Grape | 17% | ||||||
West | Last 4 Weeks | Group Apple | Apple | 26% | 26% | 2 | ||||
West | Last 4 Weeks | Product Red Apple | Apple | 27% | 27% | 1% | 1 | |||
West | Last 4 Weeks | Product Green Apple | Apple | 22% | ||||||
West | Last 4 Weeks | Group Grape | Grape | 13% | 13% | |||||
West | Last 4 Weeks | Product Purple Grape | Grape | 16% | 16% | 3% | 1 | |||
West | Last 4 Weeks | Product Green Grape | Grape | 19% | ||||||
West | Last 13 Weeks | Group Apple | Apple | 14% | 14% | 1 | ||||
West | Last 13 Weeks | Product Red Apple | Apple | 18% | 18% | 4% | 1 | |||
West | Last 13 Weeks | Product Green Apple | Apple | 11% | ||||||
West | Last 13 Weeks | Group Grape | Grape | 25% | 25% | |||||
West | Last 13 Weeks | Product Purple Grape | Grape | 21% | 21% | -4% | ||||
West | Last 13 Weeks | Product Green Grape | Grape | 26% | ||||||
East | Last 4 Weeks | Group Apple | Apple | 12% | 12% | |||||
East | Last 4 Weeks | Product Red Apple | Apple | 10% | 10% | -2% | ||||
East | Last 4 Weeks | Product Green Apple | Apple | 18% | ||||||
East | Last 4 Weeks | Group Grape | Grape | 25% | 25% | |||||
East | Last 4 Weeks | Product Purple Grape | Grape | 21% | 21% | -4% | ||||
East | Last 4 Weeks | Product Green Grape | Grape | 13% | ||||||
East | Last 13 Weeks | Group Apple | Apple | 19% | 19% | |||||
East | Last 13 Weeks | Product Red Apple | Apple | 14% | 14% | -5% | ||||
East | Last 13 Weeks | Product Green Apple | Apple | 9% | ||||||
East | Last 13 Weeks | Group Grape | Grape | 22% | 22% | |||||
East | Last 13 Weeks | Product Purple Grape | Grape | 29% | 29% | -7% | ||||
East | Last 13 Weeks | Product Green Grape | Grape | 22% |
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |