show context of a column from a table with year dimnsion in a visual table with month-year dimension

[MaryamHa]

Hi

I am new with Power BI. I tried to simplify the problem I am facing:

I have a table (Companies-Sales-Yearly) with data which are actualized yearly. Its columns are: Company name, sales, Clustering (AA, A, B, C)), date (1st Jan 2020, 1st Jan 2021, 1st Jan 2022...)

Company name	Date	Clustering	Sale
XXXX	2020	A
XXXX	2021	AA
YYYY	2020	B
YYYY	2021	A
ZZZZ	2020	c
ZZZZ	2021	c

I have also another table (Companies-monthly) which contains monthly data. Its column are:  company name, monthly sales, date (1st Jan 2020, 1st Feb 2020, 1st Mar 2020…),….

Company name	Date	month	Sale
XXXX	2020	01
XXXX	2020	02
….	…	…
XXXX	2021	12
yyyy	2020	01
….	….

I also have a Calendar- table with month, year dimensions. I create 1:n relationship from my Calendar-table to both fact tables (Companies-Sales-Yearly, Companies-monthly).

I also have another dimension table containing distinct company names. This table has also 1:n relation with other two fact tables.

In a visual table I would like to show the following table:

Company name	Year	month	Monthly sale	Clustering
XXXX	2020	Feb	…	A
YYYY	2020	Feb	…	B
XXXX	2020	Mar	….	A

I tried the following measure to show yearly clustering of each company, which should remain unaffected by month filter.

Calculate(selectedvalue(‘Company-Sales-Yearly’[clustering]), Allexcept(calendar-Table, Calendar-Table[year]))

For the companies which have different clustering (for example Clustering of XXXX in 2020 is A and in 2021 is AA) only blank is given back in column “clustering” in the visual table.

But for companies which have the same clustering for both years the visual table shows the clustering in the clustering column.

will be grateful to hear from you 

[v-rongtiep-msft]

Hi @MaryamHa ,

Please have a try.

Cancel the relationship between these two tables.

Create a measure.

Measure = CALCULATE(MAX('Companies-Sales-Yearly'[ClusteringSale]),FILTER(ALL('Companies-Sales-Yearly'),'Companies-Sales-Yearly'[Date]=SELECTEDVALUE('Companies-monthly'[_Year])&&'Companies-Sales-Yearly'[Company name]=SELECTEDVALUE('Companies-monthly'[Company name])))

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@MaryamHa , Expected output is not clear. need example

but see if these can help

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...