How to summarize multiple columns which are filtered by different parameters???

[Anonymous]

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

[Zubair_Muhammad · Accepted solution]

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 · Accepted solution]

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]

Hello Muhammad,

thank you... Your solution was very helpful.

Best regards,

Bojan

[Zubair_Muhammad · Accepted solution]

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

@Anonymous

See the attached file here

(With your sample data)