Summarize with groupby based on many columns

[Anonymous]

Hello,

I'm looking for help with following problem:

- I have a table 

- I want to get the following:

which is basically summary of entry table.

To achieve my goal i'm doing several operations and i think that it can be limited but i don't know how. So what i do is:

1. I'm creating 3 new tables with two columns Person, Percent using following dax (where 1 is substituted 2 more times to get appropriate tables):

SUMMARIZE(FILTER(Test;Test[Person1]<>Blank());Test[Person1];"Person1SUM";SUM(Test[Percent]))

2. Then i do the UNION:

MidTable = UNION(Person1;Person2;Person3)

3. Finally i do summarize of mid table to get desired result:

SummaryTable = SUMMARIZE(MidTable;MidTable[Person1];"Suma";Sum(MidTable[Person1SUM]))

Can someone please help me to create better solution because this one is awful 🙂

[Anonymous]

In previous DAX function we had percenatge for whole group (two columns). I would like to add two more columns to slice data by month and year.

What i did so far is:

1. Added two columns (used first part of your dax formula):

Test1 = 
    FILTER (
        UNION (
            SELECTCOLUMNS ( Sheet1; "Person"; [Person1]; "Year";[Year ];"Month";[Month]; "Suma"; [Percent ] );
            SELECTCOLUMNS ( Sheet1; "Person"; [Person2]; "Year";[Year ];"Month";[Month]; "Suma"; [Percent ] );
            SELECTCOLUMNS ( Sheet1; "Person"; [Person3]; "Year";[Year ];"Month";[Month]; "Suma"; [Percent ])
        );
        [Person] <> BLANK ()
    )

and result table looks like below:

In upper table i have two years

2. Now i can add a chart that presents data in right way when i use all data but when i slice it by year then it divides percentage by 2:

3. Sliced data should have below values:

4. I don't know if i can add any sheet so i am attaching data as a print screen:

[Zubair_Muhammad]

@Anonymous 

May be a one step calculation like this

Calc Table =
VAR temp =
    FILTER (
        UNION (
            SELECTCOLUMNS ( Table1, "Person", [Person1], "Suma", [Percent] ),
            SELECTCOLUMNS ( Table1, "Person", [Person2], "Suma", [Percent] ),
            SELECTCOLUMNS ( Table1, "Person", [Person3], "Suma", [Percent] )
        ),
        [Person] <> BLANK ()
    )
RETURN
    SUMMARIZE (
        temp,
        [Person],
        "Suma", SUMX ( FILTER ( temp, [Person] = EARLIER ( [Person] ) ), [Suma] )
    )

[Anonymous · Accepted solution]

@Zubair_Muhammad

That's excatly what i have been looking for!

Thank you very much for your time and effort in writing desirable DAX function!

[Anonymous]

Subject is not dead yet:(

I'm trying to add more columns to Calc Table based on solution given by @Zubair_Muhammad but i can't achive my goal. 

What i have right know is below:

Calc Table =
VAR temp =
    FILTER (
        UNION (
            SELECTCOLUMNS ( Table1, "Person", [Person1],"Year", [Year],"Suma", [Percent] ),
            SELECTCOLUMNS ( Table1, "Person", [Person2],"Year", [Year],"Suma", [Percent] ),
            SELECTCOLUMNS ( Table1, "Person", [Person3],"Year", [Year],"Suma", [Percent] )
        ),
        [Person] <> BLANK ()
    )
RETURN
    SUMMARIZE (
        temp,
	[Year]
        [Person],
        "Suma", SUMX ( FILTER ( temp, [Person] = EARLIER ( [Person] ) ), [Suma] )
    )

Upper modifiaction returns additional column "YEAR" but values for each person are the same in each year. I've tried some modifications inside SUMX function

SUMX ( GROUPBY(FILTER ( temp, [Person] = EARLIER ( [Person] ) ),[Year]), [Suma] )

 but with no results.

Can anyone please give me any advice on how to add additional columns?

Best regards

[Zubair_Muhammad]

Hi
Could you copy paste some sample data with expected results?
I will write something for you in the morning.