Forum Discussion
Distinct count subtotal
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!
12 Replies
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/823601Hi 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 ?
One top of count distinct, you need to have sum. In case it is number use base column to get distinct count
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.
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
Can anyone help?
