Total not Matching

[harirao]

Hi All,
After including DISTINCTCOUNT & Filter in measure, Total is not coming correctly.

Correct total is 209, but getting 206

61
31
27
25
17
16
11
7
7
7
Total209

Please help me to get correct answer.

Thank you.

Regards,
Hari

[v-kelly-msft]

Hi  @harirao ,

Try to add a measure as below:

measure=sumx(summarize('table',[subregion],"%6",[%6week_1&2],[%6])

Check whether it works.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[harirao]

Hi @v-kelly-msft 

I am getting error while creating measure, as mentioned 


Regards,
Hari

[harirao]

Hi @v-kelly-msft 

I tried with below measure, was able to fine that three subregion having same ID which is creating issue for WW
Correct Answer is 89(59+9+21)


Other Subregions are getting correct answer (15+19+5+27+6+28+7=107)



Measure of %6week_1&2

%6week_1&2 = CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]), FILTER(Query1, Query1[actual_visibility_weeks]> 0))

+

CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]), FILTER(Query1, Query1[0 visibility exclusions]= "No"),

FILTER(Query1, Query1[actual_visibility_weeks]= 0),

FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N"))

 

Can you please help me to get correct result for WW.

Thank you

Regards,
Hari 

[v-kelly-msft · Accepted solution]

Hi @harirao ,

It's a subtotal issue,pls add a measure as below:

_%6week_1&2 = SUMX(SUMMARIZE('Query1','Query1'[subregion]),'Query1'[%6week_1&2])

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans]

It is nearly impossible for me to assist when 50% of your measure is blanked out.

What I suspect is happening though is your total, which is ignoring the subregion, has some duplicate data without the subregion in the filter context, so DISTINCTCOUNT is getting rid of those 3 records.

Totals in a matrix or table are not totaling the data above, but redoing the measure over the entire table.

If you need further help, please post some data and your actual measure.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[harirao]

Hi @edhans,

Please find the sample data as well as actul measure for your reference.

reportdate	actual_visibility_Weeks	0 visibility exclusions	1MUSD Flag	PipelineExclusionIndicator	subregion	OpportunityId
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	0	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	2	Yes	0	N	North West Europe	OPE-0006287232
5/25/2020	3	Yes	0	N	WW	OPE-0006287232
5/25/2020	4	Yes	0	N	WW	OPE-0006287232
5/25/2020	5	Yes	0	N	WW	OPE-0006287232
5/25/2020	6	Yes	0	N	WW	OPE-0006287232
5/25/2020	7	Yes	0	N	WW	OPE-0006287232
5/25/2020	8	Yes	0	N	WW	OPE-0006287232
5/25/2020	9	Yes	0	N	WW	OPE-0006287232
5/25/2020	10	Yes	0	N	WW	OPE-0006287232
5/25/2020	11	Yes	0	N	WW	OPE-0006287232
5/25/2020	12	Yes	0	N	WW	OPE-0006287232
5/25/2020	13	Yes	0	N	WW	OPE-0007366622
5/25/2020	14	Yes	0	N	North America	OPE-0007366622
5/25/2020	0	Yes	0	N	Europe South	OPE-0007366622
5/25/2020	25	Yes	0	N	North America	OPE-0007366622
5/25/2020	25	Yes	0	N	Europe South	OPE-0007366622
5/25/2020	25	Yes	0	N	Europe South	OPE-0007366622
5/25/2020	25	Yes	0	N	North America	OPE-0007366622
5/25/2020	25	Yes	0	N	Europe South	OPE-0007366622
5/25/2020	25	Yes	0	N	CERTA	OPE-0007366622
5/25/2020	25	Yes	0	N	Europe South	OPE-0007366622
5/25/2020	0	No	1	N	APAC	OPE-0011203108
5/25/2020	0	No	1	N	APAC	OPE-0011203108
5/25/2020	1	No	1	N	CERTA	OPE-0010571141
5/25/2020	1	No	1	N	CERTA	OPE-0010571141
5/25/2020	5	No	1	N	India	OPE-0005645198
5/25/2020	7	No	1	N	India	OPE-0005645198
5/25/2020	2	Yes	1	N	Latin America	OPE-0010964870
5/25/2020	1	Yes	1	N	Latin America	OPE-0011184958
5/25/2020	60	Yes	1	N	Latin America	OPE-0010542404
5/25/2020	60	Yes	1	N	Latin America	OPE-0010542412
5/25/2020	116	Yes	1	N	North America	OPE-0008743286
5/25/2020	59	Yes	1	N	North America	OPE-0010959914
5/25/2020	57	Yes	1	N	North America	OPE-0008743288
5/25/2020	59	Yes	1	N	North America	OPE-0008743296
5/25/2020	59	Yes	1	N	North America	OPE-0008743270
5/25/2020	81	Yes	1	N	North West Europe	OPP-0003975830
5/25/2020	8	Yes	1	N	North West Europe	OPE-0011102824
5/25/2020	11	Yes	1	N	North West Europe	OPE-0011100421

Thank you

Regards,
Hari