Issue with sum of a DAX measure in PowerBI

[ValeriaBreve]

Dear experts,

I am seeking help as DAX is not my specialty. I have 3 tables related through the fist one below (Product Code/TP Code).

I am trying to get a measure that allows me to do this:

		2021
TP Code		10	11	12
10	line1	2	4	9
10	line2	18	36	81
30	line5	100	340	500

so practically split the units sold of the last table by the product lines of the second table. I was able to do so , only to find that when I remove the product lines and product codes from the table the sum of the measure by month/year is totally wrong vs. the total units as per last table.

What's the right way here to create a measure that will give me the units sold of each product by line with the right behaviour when a high level sum is required?

Many thanks for your help here. 

Kind regards

Valeria

Product CodeProduct NameTP Code

1	A	10
2	B	20
3	C	30
4	D	40

TP CodeLineSplit

10	line1	10%
10	line2	90%
30	line5	100%
40	line1	50%
40	line3	30%
40	line5	5%
40	line6	15%

Product CodeYearMonthUnits

1	2021	10	20
1	2021	11	40
1	2021	12	90
2	2021	10	30
2	2021	11	20
2	2021	12	10
3	2021	10	100
3	2021	11	340
3	2021	12	500

[V-pazhen-msft]

@ValeriaBreve 

You can actually create the matrix If you are using a measure as the value Please check the pbix for detail.

Some settings:

1. turn off the subtotals in the format panel

2. turn off the stepped layout

3. expand the column headers.

Paul Zheng_ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[ValeriaBreve]

Hello,

thanks, that works if you go down to the product line - what does not work is if you want the summary just by month, so the total units using this measure.

So to the lowest level of detail (line) it works, but the successive aggregations don't work I believe because the measure is not going to the row detail. 

See below the result of the cards - aggregation of the measure vs. sum of the number of units

Thanks!
Kind regards

Valeria

[V-pazhen-msft]

@ValeriaBreve 
Based on what you have now, it is not possible to builder a proper model with valid relationship.

You cannot put the Line column together with Month column in one visual since no relationship can be build between table 2 and table 3. Usually, we would have a main table include key columns and relationships with other dimension tables. In your case, all three tables are fact tables, please take a look at Star schema to help dealing with multiple tables.

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[ValeriaBreve]

Hello,

thanks, the last 2 tables are related through the 1st one (star schema) - definition of product (product code/name/trade). 

Table 2 has a many to many relationship with table one (TP are multiple in each table because even in the dimension table 1 TP code corresponds to multiple products); table 3 has a many to one relationship with table 1 at product code level (product codes are unique on table 1).

I have a measure that is somehow working by multiplying the [Second Table]Split * [Third Table]Units. This gives me the correct split by year/month by product line and trade product.

However, as soon as I want to summarize and get rid of the product line and just a total result by month, the measure does not work any longer. I am guessing that I am not at the right row context level.... but this is where I need help as  I am not an expert with DAX row context level.

Thanks for your help!

Kind regards

Valeria