Sum of a distinctcountnoblanks measure not going the way i want....

[TK421]

I have the below table:

Table Source

Make	Model	Setting 1	Setting 2	Setting 3
Make 1	Model 1	a	z	aa
Make 1	Model 1	a	z	aa
Make 1	Model 1	a	z	aa
Make 1	Model 1	a	z	bb
Make 1	Model 2	a	z	aa
Make 1	Model 2	a	z	aa
Make 1	Model 2	a	z	bb
Make 1	Model 2	a	z	bb
Make 1	Model 3	a	z	aa
Make 1	Model 3	a	z	aa
Make 2	Model 4	b	z	aa
Make 2	Model 4	b	z	bb
Make 2	Model 4	b	z	cc
Make 2	Model 4	b	z	aa
Make 2	Model 4	b	z	bb
Make 2	Model 4	b	z	cc
Make 2	Model 4	b	z	aa
Make 2	Model 4	b	z	aa
Make 3	Model 1	a	y	bb
Make 3	Model 1	a	y	aa
Make 3	Model 1	a	y	bb

i then create a visualisation that presents this:

Make	Model	Setting 1	Setting 2	No of Settings 3
Make 1	Model 1	a	z	2
Make 1	Model 2	a	z	2
Make 1	Model 3	a	z	1
Make 2	Model 4	b	z	3
Make 3	Model 1	a	y	2

where No of Settings is a Measure containing DISTINCTCOUNTNOBLANKS(Source[Setting 3])

My trouble starts when i try to get the sum of No. of settings. It never gives me the correct value - it always seems to be wildly under the actual.

the above 2 tables are mini representations of my actual data 

I need this value to use with another column i have that measures which is the most consistent Setting 3 for each Make + Model + Setting1 + Setting2 group. (had help on here to finish it off)

Any help? do i need to switch to a column rather than a measure - if so how do you get a column to do what you need when the grouping is controlled by the visualisation?

(hope this makes sense)

[TK421]

Figured it out - where you have [DistinctCount] as the 2nd part of SUMX - i swapped that out for a meausre of my own that does DISTINCTCOUNTNOBLANK of the Setting 3
Its now working and correctly summing the total of all the No of Setting 3

thanks for all your help

[TK421]

I need a sum of all the distinct counts, which should equal 10 (2+2+1+3+2) in this case - but nothing i try will get that. i have tried a variety of calculates, sumx and the like - it never gets it right.
i can't include the pbix as the above tables are extremely simplified versions of my own data which is sensitive. i wish i could share - sorry.

[tharunkumarRTK · Accepted solution]

@TK421 
Assuming you want to use the result in a card visual, you can follow the below pattern

Sumx(
addcolumns
(
Summarize(
Table,
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

if you want to keep it in the same table visual then remove the filters on the table.

Sumx(
addcolumns
(
Summarize(
ALL(Table),
Table[Make],
Table[Model],
Table[Setting 1],
Table[Setting 2]
), 
"DistnctCount", DISTINCTCOUNTNONBLANK([Table[Setting 3])
),
[DistinctCount]
)

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[TK421]

Would the above be measures or calc columns?

[tharunkumarRTK]

Its a measure. Dont forget to replace the table name with your actual table name.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[TK421]

almost there - swapped everything over from my table names but the last
[DistinctCount] reference is redlined
..and..
overal error - Function ADDCOLUMNS expects a column name argument number 4

[tharunkumarRTK]

@TK421 

can you share the pbix file?

[TK421]

as mentioned - i cant share due to the nature of the data.

from the looks of it -its trying to find a field that is called DistinctCount

most i can do is post a screenshot of the error...

[tharunkumarRTK]

@TK421 

Your measure [no.of settings 3] is giving the appropriate results as per its functionality, what is your expected result ?and share the pbix if possible.