Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
tee_tt
Frequent Visitor

Calculate Sum of multiple columns and create a new table

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?

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Untitled.png

 

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

tamerj1
Super User
Super User

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):

5.png6.png7.png8.png

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

1.png2.png3.png4.png

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] )
    )
)

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

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):

5.png6.png7.png8.png

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

1.png2.png3.png4.png

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] )
    )
)

Thank,

Perfect solution

tee_tt
Frequent Visitor

@Jihwan_Kim 
perfect solution 
thanks

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

Untitled.png

 

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.