Rankx Measure

[MichaelaMul]

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])

[MichaelaMul]

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.

[sevenhills]

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() )
)

[MichaelaMul]

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. 

[sevenhills]

The code I provided is for measure and interesting why it did not work. 

No worries, you got working as a calculated column. 🙂

[MichaelaMul]

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

[MichaelaMul]

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%