Re: combine data of two tables containing same val...

Junaid72 · ‎01-11-2018

I am trying to consolidate same set of data from two different tables into one graph, just like a pivot table in excel.

I have dates on the x axis, when i try to add number of items (just filtered numbers summed up of certain category) it produces two different bars.

How do i get this to work?

JordanJSA · ‎01-11-2018

Hi Junaid,

I would go for a simple new measure of the sum of both columns then combined, i attempted this with some mock data and believe i got the results you are looking for. Let me know how you get on!

The expression i used was Measure = SUM('Branch A'[Amount Rec'd]) + SUM('Branch B'[Amount Rec'd])

Junaid72 · ‎01-11-2018

Hi @JordanJSA

I have tried this Measure but it doesn't seem to work. I would like to see all amount columns consolidated in one Bar.

The other bar would consolidate number of instances into one. hope it makes sense.

I have tried your solution and this is how my data looks now.

JordanJSA · ‎01-11-2018

Hi Junaid,

I had to create a relationship between the two tables via the date before it would work on my example, is this possible to do on your data?

thanks

Junaid72 · ‎01-11-2018

Hi @JordanJSA

No, unfortunatley not. My data doesn't allow me to do that for some reason.

I have attached screenshots of what my data lookslike. hope it makes sense. I am just trying to get power BI to act as a Pivot table in excel and only add correct product category, filter out blanks and sum up number of instances along with summarised totals for the group.

JordanJSA · ‎01-11-2018

I think without the relationship between the date columns it won't be possible, i noticed that the date values are using different formats in one of your tables, when loading into powerbi do you have both date columns formatted correctly? also what happens when you try to create the relationship between the date columns?