Forum Discussion
How to summarize multiple columns which are filtered by different parameters???
This is the Excel sheet containing the test results of 4 subjects.
When I export the data to Power BI, I get the following columns:
-Subjects
-Test1
-Test2
-Test3
-pre1
-post1
-pre2
-post2
-pre2
-post2
Each subject has performed different tests battery. There are altogether 4 tests but each subject has done only 3 tests of them.
Now, I would like to summarize all tests in order to get 8 new columns like in the picture below:
It means that I need (SUM, AVERAGE...) of all tests, separately for pre and post measurements for each test parameter (Strength, Flexibility...).
I have tried with IF, DISTINCT, SUM and other functions to filter and summarize Tests columns (Test1,Test2 and Test3) in order to get only data from Strength across all columns in which the results of Strength parameter is present. Nothing I tried was successful.
Here is the example of IF function I have tried:
StrengthPRE = IF(Tabelle1[Test_1]="Strength";Tablle1[pre1] + IF(Tabelle1[Test_2]="Strength";Tablle1[pre2]+ IF(Tabelle1[Test_3]="Strength";Tablle1[pre3])))
Strangely, the IF function is valid but deliver the sum of only first two tests (Test1 and Test2).
Is there any simple way to obtain those 8 new columns like I presented in the second picture???
Thank you in advance,
Bojan
HI Anonymous
I have a DAX solution
Go to Modelling Tab and create New Tables which will form the Columns of your desired OUTPUT
Strength_Pre = UNION ( CALCULATETABLE ( VALUES ( TableName[pre1] ), TableName[Test1] = "Strength" ), CALCULATETABLE ( VALUES ( TableName[pre2] ), TableName[Test2] = "Strength" ), CALCULATETABLE ( VALUES ( TableName[pre3] ), TableName[Test3] = "Strength" ) )Flexibility_Pre = UNION ( CALCULATETABLE ( VALUES ( TableName[pre1] ), TableName[Test1] = "Flexibility" ), CALCULATETABLE ( VALUES ( TableName[pre2] ), TableName[Test2] = "Flexibility" ), CALCULATETABLE ( VALUES ( TableName[pre3] ), TableName[Test3] = "Flexibility" ) )And So On
Anonymous
Now you can desired OutPut by Creating Another Table which will combine the Tables you created above
Desired_Table = ROW ( "StrengthPre", AVERAGE ( Strength_Pre[pre1] ), "FlexibiliyPre", AVERAGE ( Flexibility_Pre[pre1] ), "EndurancePre", AVERAGE ( Endurance_Pre[pre1] ) )
4 Replies
- Zubair_Muhammad
Community Champion
HI Anonymous
I have a DAX solution
Go to Modelling Tab and create New Tables which will form the Columns of your desired OUTPUT
Strength_Pre = UNION ( CALCULATETABLE ( VALUES ( TableName[pre1] ), TableName[Test1] = "Strength" ), CALCULATETABLE ( VALUES ( TableName[pre2] ), TableName[Test2] = "Strength" ), CALCULATETABLE ( VALUES ( TableName[pre3] ), TableName[Test3] = "Strength" ) )Flexibility_Pre = UNION ( CALCULATETABLE ( VALUES ( TableName[pre1] ), TableName[Test1] = "Flexibility" ), CALCULATETABLE ( VALUES ( TableName[pre2] ), TableName[Test2] = "Flexibility" ), CALCULATETABLE ( VALUES ( TableName[pre3] ), TableName[Test3] = "Flexibility" ) )And So On
- Zubair_Muhammad
Anonymous
Now you can desired OutPut by Creating Another Table which will combine the Tables you created above
Desired_Table = ROW ( "StrengthPre", AVERAGE ( Strength_Pre[pre1] ), "FlexibiliyPre", AVERAGE ( Flexibility_Pre[pre1] ), "EndurancePre", AVERAGE ( Endurance_Pre[pre1] ) )- Zubair_Muhammad