Creating a new measure to display in matrix

[SMITHY10283]

New to powerbi and needed some help. 

Have a matrix visualisation where I have

regions in the column, and for the rows I have month/year. The values in the matrix shown are the counts of products for those regions sold in that month year. 

	AUS	GER	SWI	NZL
Jun 23	45	21	34	2
May 23	29	48	29	39
Apr 23	23	50	50	40
Mar 23	20	30	39	2
Feb 23	40	30	52	3

I have a new requirement to also add in a column called scope, which is a sum of the counts of products across all regions except Aus with the condition of 'In Scope' as "Yes" (In scope is a column in my data model which can either be Yes or NO) across the different month/years. 

I want this scope to be shown as a new column in the existing matrix for each month / year. 

	AUS	GER	SWI	NZL	Scope
Jun 23	45	21	34	2	144
May 23	29	48	29	39	140
Apr 23	23	50	50	40	130
Mar 23	20	30	39	2	130
Feb 23	40	30	52	3	120

The reason why I want to do this is so I can create a line bar chart. The bars would show the original matrix and the line would show the scope. 

Is this the right approach and if so how would I be able to do it? 

[Ashish_Mathur]

Hi,

For Mar 2023 (and may be for other months as well), how can the numbers in the Scope column > sum of numbers across all regions?

[v-zhangti · Accepted solution]

Hi, @SMITHY10283 

Based on the data you have provided, please check if this is the result you expect.

Count = SUM('Table'[Value])//Try to restore your data.

Measure = 
Var _table=SUMMARIZE('Table','Table'[Date],'Table'[Region],"Count",[Count])
Var _Sum=SUMX(FILTER(_table,[Region]<>"AUS"),[Count])
Return
IF(HASONEVALUE('Table'[Region]),[Count],_Sum)

Please check the annex and provide more data if there are discrepancies.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @SMITHY10283 ,

I have to admit that I have no idea what you are asking. Please, consider creating a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the file, to onddrive, google drive, or dropbox and share the link in this thread. Make sure that the file can be downloaded without without logging in. If you are using a spreadsheet to create the sample data instead of the manual input method, share the spreadsheet as well.

The most important thing, though, describe the expected output based on the sample data, e.g. how the measure relate to a line and stacked column chart / line and clustered column chart. I assume you are referencing one of former data visualization types with " ... line bar chart ... .".

Regards,

Tom 

[SMITHY10283]

Hi @TomMartens 

Does that make more sense?