Hi.
I made a summarized table with the following dax. However, there are some duplicated rankings even if the values are not similar.
Can you help me to solve the problem?
Ranking - Region =
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _RefYear = IF(_month > 6, _year, _year - 1)
RETURN
ADDCOLUMNS(
SUMMARIZE(
FILTER(
'_SALES',
(YEAR('_SALES'[Transaction Date]) = _RefYear &&
RELATED(_CUSTOMER[Division Code]) IN {"1", "2", "3"} &&
RELATED(_GROUP[Product Group Code]) = "A") &&
'_SALES'[USD] > 0
),
_CUSTOMER[Region Index],
"Sales", SUM('_SALES'[USD]),
"Year", _RefYear
),
"Region Rank",
RANKX(
SUMMARIZE(
FILTER(
'_SALES',
(YEAR('_SALES'[Transaction Date]) = _RefYear &&
RELATED(_CUSTOMER[Division Code]) IN {"1", "2", "3"} &&
RELATED(_GROUP[Product Group Code]) = "A") &&
'_SALES DATA'[USD] > 0
),
_CUSTOMER[Region Index],
"Sales", SUM('_SALES DATA'[USD])
),
[Sales],
,
DESC,
DENSE
)
)
Region Index is a unique value in text.
Example result,
Region Index Ranking Sales
A 20 4541889.2
B 20 4681260.92
I don't think the A and B regions are the same ranking.
Please help me to solve this problem.
