Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summarize with groupby based on many columns

Hello,

I'm looking for help with following problem:

- I have a table 

data.PNG

 

 

 

 

 

 

 

 

 

- I want to get the following:

data1.PNG

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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!

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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:

table.PNGIn 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:

Charts.png

3. Sliced data should have below values:

RightData.PNG

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

RawData.PNG

Zubair_Muhammad
Community Champion
Community Champion

@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] )
    )

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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
Not applicable

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

 

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.