Distinct count subtotal

[superDAX]

Hello All 

I am a newbie to Dax !,

The answer to my query is likely very simple 

I have performed a distinct count on all values in a column which are higher than zero 

However, the pivot table does not correctly subtotal for each group and i am not sure of how to change the measure to make it do this! 

The measure I have used for the column "No of of forecasters who input" is CALCULATE(DISTINCTCOUNT(Table1[Number of Forecasters Who Input]),FILTER(Table1,[Number of Forecasters Who Input]>0))

This measure does not include any formula to make the column include subtotals but i thought this would occur automatically as this is shown in a power pivot.

For example for the group sales people the sub total should say 2 as only Anna and micheal have number 1 as a value in the column number of forecasters who input. The sub total instead shows 1 for all groups

Please Help!

[v-frfei-msft]

Hi @superDAX ，

Please create a measure as below.

Measure = SUMX(table1,[Number of Forecasters Who Input])

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[superDAX]

Hello all 

Please see my data @v-frfei-msft @amitchandak 

I have highlighted in my power pivot the column data required and the current measure being used. The measure shows the value 1 against any sales person who has completed a review , this has been achieved by filtering for a distinct count of any value above zero and displaying how many of these values exist in each row, so any number that is not 0 in any row will return 1. 

I would like to subtotal this for each column so that the measure for the sales people group should show 2 as there are 2 people in this group that performed a number of reviews rather than zero. The column  highlighted in green represents the results i wish to see 

[superDAX]

Can anyone help?

[v-frfei-msft]

Hi @superDAX ,

What does your measure look like? Could you please share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[superDAX]

Hello

Please see the link to my data

https://1drv.ms/x/s!Ap_1IL9xapL2g0mgMGrbFgDAiN4C?e=eaQLbJ

[superDAX]

can anyone help?

[KarenL7]

Hi

Did you have solution for this, I had the same problem and have been looking at the same issue of the subtotals not working for a count in a matrix.

Thanks

Karen

[amitchandak]

Number of Forecasters Who Input is the number field of the name field. Because if it is a number field the distinct count of number is happening.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[superDAX]

Hi @amitchandak 

Thank you for replying 

I have used a measure to get the distinct count , i tried changing the original columns format to general but the measures subtotal still shows only 1 ?

[amitchandak]

One top of count distinct, you need to have sum. In case it is number use base column to get distinct count

[superDAX]

Hi @amitchandak 

How would you write this into the measure? , currentlu the measure is CALCULATE(DISTINCTCOUNT(Table1[Forecast Reviewed]),FILTER(Table1,[Forecast Reviewed]>0))

[amitchandak]

I am not sure on the values in [Forecast Reviewed], based on the values it can sum, count distinct or we need to take count distinct of name

CALCULATE(sum(Table1[Forecast Reviewed]),FILTER(Table1,[Forecast Reviewed]>0))

CALCULATE(DISTINCTCOUNT(Name]),FILTER(Table1,[Forecast Reviewed]>0))