Merging and pivoting two tables

[Anonymous]

Please can someone help me to create a clustered bar chart using data from two tables. The ‘GTR’ table looks like this:

GTR Table
Name	C4L MB	C4L MLE
Craig	1	2
John	3	4

The second table ‘LW’ looks like this:

LW Table
Name	C4L MB	C4L MLE
Craig	1	1
Craig	2	3
John	3	2
John	4	4

I think that I need to create a table in Power BI that looks like this:

Name	Rubric	GTR Score	LW Score (=Average of ‘LW’ score for that individual and column)
Craig	“C4L MB”	1	1.5
Craig	“C4L MLE”	2	2
John	“C4L MB”	3	3.5
John	“C4L MLE”	4	3

So that I can then create a clustered bar chart that is filtered for Craig or John that look like these:

Bar chart for CraigBar chart for John

Your assistance would be much appreciated.

Thanks Steve

[amitchandak · Accepted solution]

Add one static column to each table, prefer to add custom in edit query

Type = GTR

TYPE = GW

Then use unpivot: https://radacad.com/pivot-and-unpivot-with-power-bi

Then append data: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Now you can use filter in formula and take avg or min max

calculate(Avg(Table[Score]),Table[Type]="GW")

I assumed score is the column, where numeric data moved

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[amitchandak]

@Anonymous , If the above solutions worked out for you ?

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on my research, you may click 'Edit Query', go to Query Editor, right-click GTR table and choose 'duplicate'.

Then you can make 'C4L MB' and 'C4L MLE' selected, go to 'Transform' ribbon, click 'unpivot columns', rename the columns and click 'Close and Apply' in 'Home' ribbon.

Then you may create a measure as follows.

LW Score = 

IF(
    MAX('GTR Table (2)'[Rubric]) = "C4L MB",
    CALCULATE(
        AVERAGE('LW Table'[C4L MB]),
        FILTER(
            ALLSELECTED('LW Table'),
            'LW Table'[Name] = MAX('GTR Table (2)'[Name])
        )
    ),
    IF(
        MAX('GTR Table (2)'[Rubric]) = "C4L MLE",
        CALCULATE(
            AVERAGE('LW Table'[C4L MLE]),
            FILTER(
                ALLSELECTED('LW Table'),
                'LW Table'[Name] = MAX('GTR Table (2)'[Name])
            )
        )
    )
)
    

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your help as well. I needed some of your response as well to get to a solution.

Steve

[amitchandak · Accepted solution]

Add one static column to each table, prefer to add custom in edit query

Type = GTR

TYPE = GW

Then use unpivot: https://radacad.com/pivot-and-unpivot-with-power-bi

Then append data: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Now you can use filter in formula and take avg or min max

calculate(Avg(Table[Score]),Table[Type]="GW")

I assumed score is the column, where numeric data moved

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Thanks very much, I used the Unpivot and Append elements of your answer - which were much appreciated, but there were some elements that didn't take me in the right direction.

Steve