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
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.
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
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.
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.
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 @
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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
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))
Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.
Click here to read more about the April 2022 updates!
Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!