Distinct Count of Material going wrong at Grand Total level of a group

[Anonymous]

Hello experts,

I am facing an issue with Distinct Count at grand total. Please find the below screenshot and help me. I have used below formula for bucketing.

21-45 Dayss =

var a =  'Planning- DOI'[DOI]

var b = CALCULATE(sumx(values('Planning- DOI'[Parent Material.Parent Material Level 01.Key]), a))

var c = if(AND((b>=21 && b<=45), 'Planning- DOI'[DOI]>0),int(1),0)

var d = IF(HASONEVALUE('Planning- DOI'[Parent Material.Parent Material Level 01.Key]), c, SUMX(VALUES('Planning- DOI'[SCC.SCC Level 01.Key]), c))

return d

21-45 Dayy = IF(HASONEVALUE('Planning- DOI'[Parent Material.Parent Material Level 01.Key]), 'Planning- DOI'[21-45 Dayss], SUMX(VALUES('Planning- DOI'[Parent Material.Parent Material Level 01.Key]), 'Planning- DOI'[21-45 Dayss]))

For 21-45 Dayy measure at sub total level the values are displayed correct but at Grand total level the total count should be 3 since there are only 3 distinct values. 

Regards,

Ajay

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[amitchandak · Accepted solution]

@Anonymous , For correct grand total, you need measure like

Sumx(Summarize('Planning- DOI','Planning- DOI'[Parent Material.Parent Material Level 01.Key]), [21-45 Dayy])

you can add another column in row of matrix to summarize

refer: https://www.youtube.com/watch?v=ufHOOLdi_jk

[Anonymous]

Dear @amitchandak ,

Thanks for the immediate response. Please find the below screenshot and a sample file. 

Sample file 

Data file 

Regards,

Ajay

[Ashish_Mathur]

Hi,

This question can be solved with just 2 measures.  See the second table in the screenshot.  You may download my PBI file from here.

Hope this helps.