cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hgzelaya Regular Visitor
Regular 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 Regular Visitor
Regular 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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 115 members 1,739 guests
Please welcome our newest community members: