Using report view values to calculate another value

[AP23]

lo,

I'm quite new to Power BI so apologies if this is a silly question but I cannot find the solution to something I thought would be easy in the same way I would do it in Excel.

In Report view, I have a column which uses DAX to Sum it's value (from thousand of rows). I have another column which pulls through a static value from another table. Both are displayed in Report View based on SiteID.

I would like to do a further calculation whereby I want to display the value of Column A minus (-) column B = column C.

I can't get this to work at table level as I end up with duplicated rows showing in Report View, i think because final values are currently being automatically summed in the Report View.

Can I do this at Report level please?

[AP23]

Just to add, if I use TableC[SiteID] on the left side of the = operator, it then doesn't show TableA[SiteID] on the right side of the = operator.

[devesh_gupta · Accepted solution]

@AP23 Refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another:
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

If you find this insightful, please provide a Kudo and accept this as a solution.

[devesh_gupta]

@AP23 I'm unable to get your last point but let's try to create a simple measure from these two columns just like:

measure = sum('table'[column A]) - sum('table'[column B])

Or else if it doesn't work, can you provide me the sample data for the same.

If you find this insightful, please provide a Kudo and accept this as a solution.

[AP23]

Many thanks for your help.

Having looked at this further, I think the issue is to do with my table relationships.

I have the following structure:

Table A      <->1:1      Table B      ->1:many      Table C

10 rows                       10 rows                          1,000 rows

I want to add a column in Table A whereby it sums the multiple values of a column in Table C. 

However, the formula I use doesn't give me the option to filter based on column "Site ID" .

I want to use: CALCULATE(SUM(TableC[column1]),TableA[SiteID]=TableC[SiteID]))

However, I it doesn't give me the option to locate TableC[SiteID] when used on the right side of the = operator. It does, however, show it if I use it on the left hand side of the = operator.

I have tried changing my table relationships so that it becomes the following but it makes no difference.

Table A      <-1:many      Table B      ->1:many      Table C

10 rows                          10 rows                          1,000 rows

Is there a command I can use to pull in the TableC[SiteID] value in my CALCULATE equation? I've tried RELATED but this hasn't worked.

Any help much appreciated! thank you.