Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |