topic Hierarchical Ranking Positive to Negative values in DAX Commands and Tips

Hierarchical Ranking Positive to Negative values

Anonymous — Tue, 14 Feb 2023 23:17:01 GMT

Hello pbi community,

My question may have an easy solution, but I haven't been able to figure it out.

Essentially I wan to display a ranking in a hierarchical form rolling a value from its respective store then to the region.
The problem I'm facing is that when passing from positive to negative values at a region level, the matrix creates a new blank row and assigns it a rank number.
Below I'm providing images about the issue ;

Here is how the table looks like without the ranking measure;

The ranking measure is working well at a store level except at the strange blank row;

Here is the dax to created to calculate the ranking:

Op Income Rank =

var rank_region = RANKX(ALL('Location'[Region]), [Operating Income], , DESC)

var rank_store = RANKX(FILTER(ALL('Location'[Store Name], 'Location'[Region]), 'Location'[Region] = MAX('Location'[Region])), [Operating Income], , DESC)

return

SWITCH(TRUE(),

ISINSCOPE('Location'[Store Name]), rank_store,

ISINSCOPE('Location'[Region]), rank_region,

BLANK() )

Finally, the data model looks like this, where I'm joining Location and Income tables with Store_number columns;

In advance thank you so much for your help!

Re: Hierarchical Ranking Positive to Negative values

Greg_Deckler — Tue, 14 Feb 2023 23:28:12 GMT

Anonymous Sample data please. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Hierarchical Ranking Positive to Negative values

Anonymous — Wed, 15 Feb 2023 15:41:35 GMT

Sorry for not posting a sample dataset before.

Here is a sample data for Locations and Income tables;
Location Table:

Region	Store	Store Number
California Bay Area	Newark	1
California Bay Area	North - San Jose	2
California Bay Area	Windsor	3
California Bay Area	Fairfield	4
California Bay Area	Oakland	5
California Bay Area	Richmond	6
California Bay Area	American Canyon	7
Canada Alberta	Kelowna	8
Canada Alberta	Edmonton	9
Canada Alberta	Calgary 52nd St	10
Canada Alberta	Calgary	11
Central Valley	Salinas SJP	12
Central Valley	Modesto	13
Central Valley	Stockton	14
Central Valley	Moss Landing	15
Central Valley	Merced	16
Central Valley	Fresno	17
Central Valley	Salt Lake City	18
North Mid West NE	Columbus	19
North Mid West NE	Summit	20
North Mid West NE	Kansas City-12th Street	21
North Mid West NE	Kansas City-Winner Road	22
North Mid West NE	Cumberland	23
North Mid West NE	Hammond	24
North Mid West NE	Kansas City	25
North Mid West NE	Johnston	26
Sacramento Valley/Nevada	Rocklin	27
Sacramento Valley/Nevada	Antelope	28
Sacramento Valley/Nevada	Carson City	29
Sacramento Valley/Nevada	Chico	30
Sacramento Valley/Nevada	Redding	31
Sacramento Valley/Nevada	Rancho Cordova	32
Sacramento Valley/Nevada	Sparks	33
Sacramento Valley/Nevada	Sacramento Mather	34
South Mid West Texas	Elliot Reeder-Ft. Worth	35
South Mid West Texas	Dallas South	36
South Mid West Texas	Little Rock	37
South Mid West Texas	Dallas South Central	38
South Mid West Texas	Springfield	39
South Mid West Texas	Virginia Beach	40
South Mid West Texas	San Antonio	41
South Mid West Texas	Tallahassee	42
South Mid West Texas	St Louis	43
Washington Oregon	Tacoma	44
Washington Oregon	Arlington	45
Washington Oregon	Tumwater	46
Washington Oregon	Lynnwood	47
Washington Oregon	South Portland	48
Washington Oregon	Vancouver	49
Washington Oregon	Sherwood	50

Income table:

Store Number	Amt
1	372748
2	226048
3	49756
4	-7825
5	-34342
6	-253424
7	-348797
8	-237753
9	-283067
10	-407082
11	-420050
12	73253
13	66690
14	53339
15	-56247
16	-96195
17	-197111
18	-197501
19	-191547
20	-269625
21	-323713
22	-329673
23	-381504
24	-385052
25	-407029
26	-507863
27	-124296
28	-127783
29	-129569
30	-203591
31	-211544
32	-217815
33	-237405
34	-321198
35	35003
36	-18907
37	-20411
38	-186756
39	-199024
40	-216219
41	-307012
42	-338154
43	-372394
44	-10049
45	-63729
46	-122354
47	-145239
48	-180105
49	-263666
50	-337834

both Locations and Income tables joined by Store_Number.

Here are images for what is expected;

Expanded matrix:

Collapsed matrix:

Instead, I'm getting this in Power BI:

I don't understand why the transition from positive to negative 'Operating Income' generates a blank row in the matrix and assignes it a rank = 2.

Dax used to create the measure 'Op Income Rank':

Op Income Rank = var rank_region = RANKX( ALL('Location'[Region]), [Operating Income], , DESC ) var rank_store = RANKX( FILTER( ALL('Location'[Store Name], 'Location'[Region]), 'Location'[Region] = MAX('Location'[Region]) ), [Operating Income], , DESC ) return SWITCH(TRUE(), ISINSCOPE('Location'[Store Name]), rank_store, ISINSCOPE('Location'[Region]), rank_region, BLANK() )

Measure 'Operating Income' was previously calculated, it doesn't present any issue.

I have researched this issue in the web, but no body had this problem before. I may be doing something wrong, but I can not see what is it.

Thanks in advance!