Forum Discussion
Distinct Count - How to get correct row level figures?
Hello,
I'm struggling a lot with this : Calculating a distinct count at the row level doesn't seem to work. Only the total is distinct.I have a table that aggregates a list of accounts by dimensions (region / industry / acc class / motion ).
Im aggregating at the subsidiary level so there's duplicate values. Hence the Distinct Count.
Small example here (real world has more columns) :
| Region Lvl 1 | Region Lvl 2 | Distinct Count of PE_ID |
| APJ | ANZ | 21972 |
| APJ | INDIA | 63348 |
| APJ | JAPAN | 41655 |
| APJ | KOREA | 16198 |
| APJ | SEA | 69012 |
| Grand Total | 212182 |
Expected outcome - each row when summed equals the distinct count total
I have tried already the following measure but that doesn't seem to change the row level value.
Test_Distinct =
VAR T = SUMMARIZE(_MAL,_MAL[GEO_LEVEL1], _MAL[Region Lvl 2 NA], _MAL[PE MOTION V2], _MAL[PE_SAP_MASTER_CODE_TEXT], _MAL[Internal Account Classification (PE)], "PE Distinct", DISTINCTCOUNT(_MAL[PE_ID]))
RETURN
SUMX(T,[PE Distinct])
Appreciate any help
Quentin
qmartiny , Try a measure like
Sumx(Summarize(Table, Table[Region Lvl 1],[Region Lvl 2], "_1",DistinctCount(Table[PE_ID])),[_1])
1 Reply
- amitchandakSuper User
qmartiny , Try a measure like
Sumx(Summarize(Table, Table[Region Lvl 1],[Region Lvl 2], "_1",DistinctCount(Table[PE_ID])),[_1])