Forum Discussion

Mbechet's avatar
Mbechet
Frequent Visitor
9 years ago
Solved

Total and filter count distinct values

Hi all, I have a matrix on which I am showing the total count of each value occurences for each row and colu + the count of distinct values. My goal is to get in the matrix only the values for whic...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI Mbechet,

     

    I modified your formula and it seems work on my side.

     

    Custom = 
var currentAgent=LASTNONBLANK(Sheet1[Agent],[Agent])
return
if(COUNT(Sheet1[Amount])=COUNTX(FILTER(ALL(Sheet1),[Agent]=currentAgent),[Amount])||COUNT(Sheet1[Amount])=COUNTX(ALL(Sheet1),[Amount]),DISTINCTCOUNT(Sheet1[Amount]),COUNT(Sheet1[Amount]))

     

    Notice: if statement check agent group to filter on total row, so if your agent has only one amount category, distinct count formula will also calculate on that row.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Mbechet,


I think I have clear your requirement, normal column show total amount, and at total column switch to distinct mode, right?

It seems like I can't download for your sharing link, can you please upload this file to onedrive?

 

Sample:

 

 

Regards,
Xiaoxin Sheng

Mbechet's avatar
Mbechet
Frequent Visitor
9 years ago

Hello Anonymous

Yes you got it right but I am not able to figure out how to apply it to my model.

You can download the files here.

 

Thanks

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI Mbechet,

     

    I modified your formula and it seems work on my side.

     

    Custom = 
var currentAgent=LASTNONBLANK(Sheet1[Agent],[Agent])
return
if(COUNT(Sheet1[Amount])=COUNTX(FILTER(ALL(Sheet1),[Agent]=currentAgent),[Amount])||COUNT(Sheet1[Amount])=COUNTX(ALL(Sheet1),[Amount]),DISTINCTCOUNT(Sheet1[Amount]),COUNT(Sheet1[Amount]))

     

    Notice: if statement check agent group to filter on total row, so if your agent has only one amount category, distinct count formula will also calculate on that row.

     

    Regards,

    Xiaoxin Sheng

    • Mbechet's avatar
      Mbechet
      Frequent Visitor
      9 years ago

      Hello Anonymous

      That works perfectly. Thank you for your very helpful support.


      Regards