Distinct Count With Agrr

[ngrunoz]

I Have A data from 2 Tables and  I want to use the data on a pivot chart where I Summarize it  as Show in  attached sheet Desired results. with the source data being sales details and Miner details.

1st requirement is to have distinct count of MinersName being affected by the Dimension =  Count(DISTINCT MinersName)

2nd requirement is to have the distinct count of MinerName not being affected by Dimension Branch so that in the event where they are no sales to a branch we can still have those miners accounted for the other Dimension Which is manager without it returning a Null Value   I have tried to use  Count( Aggr(NODISTINCT DISTINCT Only(MinersName), MinersName ,Manager)) but I an not getting desired results. its counting Miners with one record twice and also those without sales are having a Zero value

[sunny_talwar]

Try this

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager)

[ngrunoz]

Hi Sunny is it possible to get 1 for each  of the record on

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager)

instead of the subtotal and then have the correct subtotal so that in the event that I want to calculate a % and on my Selection i select a number of miners then i will be able to get a correct % only for the selected miners  not only based on the whole manager group.

[sunny_talwar · Accepted solution]

Try this

If(Dimensionality() = 2,

Aggr(NODISTINCT Count(DISTINCT [Miner name]), [Miner name]),

If(Dimensionality() = 1,

Aggr(NODISTINCT Count(DISTINCT [Miner name]), Manager),

Count(TOTAL DISTINCT [Miner name])))

[ngrunoz]

Thanks Sunny looking forward to learn much more from you.

[ngrunoz]

Hi Sunny. I had Over looked on the SubTotal for the Branches. I would not want it to be a distinct value but to be a Sum of the distinct Value for each Branch as per Attached File.

[sunny_talwar]

Pivot table is acting out weird when I try to change the expressions to accomplish this... this might be a bug or intended behavior... but not really sure if it can be done...

[ngrunoz]

Thanks