Summarize multiple columns by group into one column from sales fact table

[Tripb44]

I am needing to summarize three columns into one as shown below.  What would be the best way to do this.  All three columns are in a sales fact table.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Tripb44]

Hi this worked perfect for the initial task.  Question, I am trying to add a date field to group by YearMonth.  All seems to work as expected in the first screenshot below, however, when moving on to the next applied step I encounter all null date values.  Is there a workaround to this?  Thanks again for the great response!

[Ashish_Mathur]

Hi,

The picture is way too small.  I cannot understand anything.

[amitchandak]

@Tripb44 , Create a common Rep table  and join with three rep column, active join with rep and inactive join with other two and only join in visual using userelationship in measure

a new measure =

calculate(sum(Table[rep1$])) +
calculate(sum(Table[rep2]), userelationship(rep[rep], table[Rep2$])) +
calculate(sum(Table[rep3]), userelationship(rep[rep], table[Rep3$]))

userelationship -

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

common table - https://www.youtube.com/watch?v=Bkf35Roman8

Or create new Table

Union(
selectcolumns(Table, "Rep1", Table[rep1], "rep$",Table[rep1$]),
selectcolumns(Table, "Rep1", Table[rep2], "rep$",Table[rep1$]),
selectcolumns(Table, "Rep1", Table[rep3], "rep$",Table[rep1$])
)

Or create new Table
Union(
summarize(Table, Table[rep1], "rep$",sum(Table[rep1$])),
summarize(Table, Table[rep2], "rep$",sum(Table[rep1$])),
summarize(Table, Table[rep3], "rep$",sum(Table[rep1$]))
)

[Tripb44]

For the union, if I need to add the order date as well how would that look for the dax?