Forum Discussion

qmartiny's avatar
qmartiny
Helper III
3 years ago
Solved

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 1Region Lvl 2Distinct Count of PE_ID
APJANZ21972
APJINDIA63348
APJJAPAN41655
APJKOREA16198
APJSEA69012
   
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

  • qmartiny , Try a measure like

     

    Sumx(Summarize(Table, Table[Region Lvl 1],[Region Lvl 2], "_1",DistinctCount(Table[PE_ID])),[_1])