Need help with really hard task. Same Date and ID Relationship

[hgzelaya]

Hello everyone, i have 2 data tables. One of them including month, id, status and sales. The other data table includes month, id and Transaction. 

What i desire is to show in a clustered stacked column chart the amount of sales, count transtions and status after i have filtered these by the ID number.

Example:

Is it possible???

Data Info: 

https://docs.google.com/spreadsheets/d/1oFMtGZHqbupQrCwnVdfM0UomFkG3EzwAub027AWr5kE/edit#gid=0

First Data Table
Month	ID	Sales	Status
Jan	1	100	0
Feb	1	120	0
Mar	1	220	2
Apr	1	230	0
Jan	2	180	0
Feb	2	220	2
Mar	2	230	0
Apr	2	190	0
Jan	3	195	5
Feb	3	240	2
Mar	3	230	0
Apr	3	220	2

2nd Data Table
Month	ID	Transaction
Feb	2	A1
Feb	3	B3
Mar	1	C2

[v-lili6-msft · Accepted solution]

hi, @hgzelaya 

You could use this way:

Step1:

Add two dim tables: Dim Month table and Dim ID table

Step2:

Then create the relationship as below:

Step3:

Create three measure for "amount of sales, count transtions and status"

amount of sales = CALCULATE(SUM(Table1[Sales]))

count transtions = CALCULATE(COUNTA(Table2[Transaction]))

Status measure = CALCULATE(SUM(Table1[Status]))

From your screenshot, if it is sum(status)? if not just replace SUM(Table1[Status]) with COUNTA(Table1[Status])  

Step4:

Then use Combo Chart in Power BI, use Month field from Dim Month table as X-axis, ID field from DIm ID table as a slicer.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

Result:

here is sample pbix file, please try it.

Best Regards,

Lin

[hgzelaya]

Thank you so much it worked as i needed!