cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hgzelaya Frequent Visitor
Frequent Visitor

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

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:

2019-05-16 17_56_39-Libro2 - Excel.jpg

Is it possible???

 

 

Data Info: 

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

 

 

First Data Table
MonthIDSalesStatus
Jan11000
Feb11200
Mar12202
Apr12300
Jan21800
Feb22202
Mar22300
Apr21900
Jan31955
Feb32402
Mar32300
Apr32202

 

 

2nd Data Table
MonthIDTransaction
Feb2A1
Feb3B3
Mar1C2
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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:

4.JPG

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:

5.JPG

here is sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

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

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:

4.JPG

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:

5.JPG

here is sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hgzelaya Frequent Visitor
Frequent Visitor

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

Thank you so much it worked as i needed!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 263 members 2,984 guests
Please welcome our newest community members: