Calculate Sum of multiple columns and create a new table

[tee_tt]

I have a three tables that looks like this:

|season| production|      
|:------|:---------|
| A    | 12        |
| A    | 200       |
| A    | 40        |
| A    | 60        |


|season| production|      
|:------|:---------|
| B    | 11        |
| B    | 20        |
| B    | 400       |
| B    | 600       |


|season| production|      
|:------|:---------|
| C    | 119       |
| C    | 212       |
| C    | 466       |
| C    | 697       |

I want to have a table like this:

|seasons| Total_prodtn| Percentage_Prodtn|
|:------|:------------|:-----------------|
| A     |sum from A   | %                |
| B     |sum from A   | %                |
| c     |sum from c   |  %               |

I tried using DAX but it did not workout.

any better way to do this?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

[tamerj1 · Accepted solution]

Hi @tee_tt 
Here is sample file with the solution for you reference https://we.tl/t-KIGcZQK6DS
There are three methods to handle this situation:

The first method is to create a new calculated table using dax as suggested by @Jihwan_Kim's solution.

The second solution which I do preffer is to append the three tables using power query (Shall require no dax):

The third solution is by creating a common filter table that contains all the distinct seasons. You can create eaiter manually or using power query or using dax. Here is the solution using dax

Sesons = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( TableA, "Season", TableA[Season] ),
        SELECTCOLUMNS ( TableB, "Season", TableB[Season] ),
        SELECTCOLUMNS ( TableC, "Season", TableC[Season] )
    )
)

Total Production = SUM ( TableA[Production] ) + SUM ( TableB[Production] ) + SUM ( TableC[Production] )

% Production = 
DIVIDE ( 
    [Total Production],
    CALCULATE ( 
        [Total Production],
        ALL ( Sesons[Season] )
    )
)

[tee_tt]

Thank,

Perfect solution

[tee_tt]

@Jihwan_Kim 
perfect solution 
thanks

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.