Forum Discussion
Duplicate and Display Summary Data From Excel to PowerBI Table
Hello!
I'm attempting to recreate a summary table from Excel into Power BI. It's basically performing several SUMIFS statements across different tables and returning the results for review. The calculations are run for several different conditions (IE: only sum items that are blue in column 1, red in column 2, green in column 3). Example table below:
| Condition 1 (red) | Condition 2 (blue) | Condition 3 (green) | |
| R1 SUMIFS from table 1 | |||
| R2 SUMIFS from table 2 | |||
| R3 R1/5 | |||
| R4 R1/R2 |
I've figured out the DAX equivilent of the SUMIFS, and created measures to get a single value per cell, but I can't find a way to insert them into a table manually.
I've also dug into calculated tables, but due to the table doing different calculations every row, I haven't made much headway in that direction either. (On that note, is there a way to dynamically add columns based on how many conditions are found?)
Any help is appreciated, thanks!
Hi, tzambo
You can use union and row functions to create a new table.
Like this:
Table = var Red1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Red") var Blue1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Blue") var Green1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Green") var Red2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Red") var Blue2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Blue") var Green2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Green") return union( ROW("Calculation","Sumifs Table 1","Red",Red1,"Green",Green1,"Blue",Blue1), ROW("Calculation","Sumifs Table 2","Red",Red2,"Green",Green2,"Blue",Blue2), ROW("Calculation","Table/5","Red",Red1/5,"Green2",Green1/5,"Blue",Blue1/5), ROW("Calculation","Table/Table 2","Red",Red1/Red2,"Green2",Green1/Green2,"Blue",Blue1/Blue2) )Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _Janey
6 Replies
- v-janeyg-msft
Community Support
Hi, tzambo
You can use union and row functions to create a new table.
Like this:
Table = var Red1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Red") var Blue1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Blue") var Green1=CALCULATE(SUM('Table 1'[Value]),'Table 1'[Condition]="Green") var Red2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Red") var Blue2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Blue") var Green2=CALCULATE(SUM('Table 2'[Value]),'Table 2'[Condition]="Green") return union( ROW("Calculation","Sumifs Table 1","Red",Red1,"Green",Green1,"Blue",Blue1), ROW("Calculation","Sumifs Table 2","Red",Red2,"Green",Green2,"Blue",Blue2), ROW("Calculation","Table/5","Red",Red1/5,"Green2",Green1/5,"Blue",Blue1/5), ROW("Calculation","Table/Table 2","Red",Red1/Red2,"Green2",Green1/Green2,"Blue",Blue1/Blue2) )Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.Best Regards,
Community Support Team _Janey
Hello v-janeyg-msft!
You're a life saver, thank you so much!
One quick question before I mark accepted - While this serves my purposes now, what if in the future the number of color columns changes? Is there a way dynamically pull the columns (IE: what if someone adds a purple color to the color columns).- v-janeyg-msft
Hi, tzambo
In my formula, you can clearly see that this is a static way of constructing a table of rows and columns. So when a new color exists, you need to manually add a line of code row(...) in the union.
If you first add table columns to table1 and table2, then append the two tables, and then use the transpose function to change the condition from a column to a row, it may be dynamic, but need to rewrite several measures to meet your needs, and the results may not be the same as what you need now.
Janey
- amitchandak
Super User
tzambo ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Sure thing! Here's an example:
Raw data:Table 1
Value Condition 1 red 1 red 2 green 2 green 3 blue 3 blue Table 2
Value Condition 4 red 4 red 5 green 5 green 6 blue 6 blue Results to Replicate
Calculation Red Green Blue Sumifs Table 1 2 4 6 Sumifs Table 2 8 10 12 Table 1 / 5 .4 .8 1.2 Table 1 / Table 2 .25 .4 .5
