Yearly sales number in matrix table

[VaishnaviGandhi]

Hi Everyone, 

I want to show global sales for each make/brand and the % it hold out of total sales. 

eg., Global sales should be as per the make and brand but Yearly set sales should only show the total of that year in this case for 2017 - 92625606, 2018 - 92488194 and so on. 

But as per the below ss it not showing just the total, the formula which i use before I had a date table was this 

CALCULATE(SUM('Master 2017 - 2023'[Sales]),ALLEXCEPT('Master 2017 - 2023','Master 2017 - 2023'[Month].[YEAR}]))

but once i added a date table for another measure this measure didnt apply as it it suppose too after even changing it to the related date tabel as 

 CALCULATE(SUM('Master 2017 - 2023'[Sales]),ALLEXCEPT('Date','Date'[Date].[Year]))

Please help. 

thanks 

Vaishnavi Gandhi

[VaishnaviGandhi]

Hi @amitchandak 

I want to show global sales for each make/brand and the % it hold out of total sales. 

eg., Global sales should be as per the make and brand but Yearly set sales should only show the total of that year in this case for 2017 - 92625606, 2018 - 92488194 and so on like the below piture.

Before adding the date table  - Formula 

Yearly Set sales = CALCULATE(SUM('Master 2017 - 2023'[Sales]),ALLEXCEPT('Master 2017 - 2023','Master 2017 - 2023'[Month].[Year]))

Since I have added a date table for my other analysis, this particluar data is not presention in the above form.
After adding the date table formula

Yearly Set sales = CALCULATE(SUM('Master 2017 - 2023'[Sales]),ALLEXCEPT('Date','Date'[Date].[Year]))

What I want ultimately this dasboard is represent is ex - if i have selected asia then how is the 18.66%(2017), 18.87%(2018) etc is  distributed across all the group for all the year, refer the below picture

Please can you advise on how can I get table 1 outcome where all the sales data is for that year shows as a total and not categorical in this example distributed across region.

Thanks

Vaishnavi gandhi 

[johnyip]

@VaishnaviGandhi 
I cannot achieve your desired outcome using your sample data provided.

I have augmented your sample data to include the columns and add reasonable amounts of fake data for the illustration.

Is the below what you want to achieve as in my attached file?

You can achieve this by the following steps.

1. Create a measure using the following definition.

% Sales = 
VAR TotalSales = CALCULATE(SUM('Table'[Global Sales]),REMOVEFILTERS('Table'[Region]))
RETURN
SUM('Table'[Global Sales])/TotalSales

2. Create the two matrixs right away. Of course you may want to do some formatting to make the matrixs look nice.

Please note that I didn't take into consideration your date table in your data model, since I do not have the sample data model provided by your side.

And kindly ensure that the sample data your provided should at least include the columns that are needed for the working, or sometimes a solution cannot be given even when you have provided your desired output.

[VaishnaviGandhi]

Hi @johnyip 

PFA, apologies for sharing a data this way, I am unsure why it didn't allow me to attached a file.

Country	Region	Group	Maker/Brand	Type	Segment	Model	PowerTrain	Value	Month
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-01-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	28-02-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-03-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-04-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-05-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-06-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-07-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-08-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-09-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-10-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-11-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-12-2021
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-01-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	28-02-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-03-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-04-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	31-05-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	-	30-06-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	17	31-07-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	17	31-08-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	18	30-09-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	10	31-10-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	9	30-11-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	8	30-12-2022
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	2	31-01-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	2	28-02-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	3	31-03-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	143	30-04-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	145	31-05-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	140	30-06-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	N/A	31-07-2023
India	Asia excl.China	Tata Group	Tata	UVs/MPVs	Unclassified	{Segment} Van2 (Iris/Magic)	N/A	N/A	31-08-2023
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	19	31-01-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	8	28-02-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	9	31-03-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	3	30-04-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	13	31-05-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	13	30-06-2021
Argentina	ROW	BMW Group	BMW	Cars	C	1 Series	N/A	14	31-07-2021

[johnyip]

Can you share the data model / sample powerbi file? These are needed for the working for DAX.