Duplicate and Display Summary Data From Excel to PowerBI Table

[tzambo]

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!

[v-janeyg-msft · Accepted solution]

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

[tzambo]

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

[tzambo]

Thank you!  I was able to get it working both ways you suggested. 

[tzambo]

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

[amitchandak]

@tzambo ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.