cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
superDAX
Frequent Visitor

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!

 

DAX help.jpg

12 REPLIES 12
v-frfei-msft
Community Support
Community Support

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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 

 

Dummy Data.jpg

 

Can anyone help?

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

can anyone help?

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
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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 ?

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Hi @amitchandak 

 

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

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))
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!