Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to Solution.
That's excatly what i have been looking for!
Thank you very much for your time and effort in writing desirable DAX function!
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:
@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] ) )
That's excatly what i have been looking for!
Thank you very much for your time and effort in writing desirable DAX function!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |