substract in two table

[Anonymous]

Hi, I'm getting a demand and supply table from excel . 

Are there any method of doing calculation in power query or dax to calcualte the final value of demand-supply. 

The result will be plot in a table of heatmap. 

The value should shown in each month also. 

Thanks for the help. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

You may consider using the following steps:

1. Duplicate the table 'Demand' and 'Supply'

2. For the copied table, select the [Item], [Sub] column and unpivot the other columns.

3. Then merge them as a new table:

4. Expand Supply[Value] as [Value.1] and add custom column like:

5. then remove [value] and [value.1] and select the [Attribute] column to pivot the [Custom] column:

6. select [Aug-22] ~ [May-23] column and replace null to 0.

7. result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[djurecic]

Hi @Anonymous ,

 You could use Merge Queries in Power query to get supply and demand in the same table. Then create a calculated column that substracts one from the other.

You can create a key column of Item & Sub in each table and use those fields for the merge.

Here is some additional information:

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9

Please consider accepting as solution if this answers the question- thanks!