How to create a table from another table in PowerBI and Sum a column

kukulu Published at Dev

kukulu

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?

Mik

Solution 1 :

VAR t1=Row("season",VALUES(Table1[season]),"Total_prodtn",SUM(Table1[production]))
VAR t2=Row("season",VALUES(Table2[season]),"Total_prodtn",SUM(Table2[production]))
VAR t3=Row("season",VALUES(Table3[season]),"Total_prodtn",SUM(Table3[production]))
VAR uni=UNION(t1,t2,t3)
RETURN 
    ADDCOLUMNS(
        uni
        ,"Percentage_Prodtn",MROUND(DIVIDE([Total_prodtn],Sumx(uni,[Total_prodtn]))*100,2)
    )

Solution 2:

VAR allInOne =UNION(Table1,Table2,Table3)
        
VAR withTotal =
    ADDCOLUMNS(
        SUMMARIZE(allInOne,[season])
        ,"Total_prodtn",VAR s=[season] 
                        RETURN
                            SUMX(
                                FILTER(allInOne,[season]=s)
                                ,[production]
                            )
    )
RETURN 
    ADDCOLUMNS(
        withTotal
        ,"Percentage_Prodtn",DIVIDE([Total_prodtn],SUMX(allInOne,[production]))
    )

Collected from the Internet

Please contact [email protected] to delete if infringement.