Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merging and pivoting two tables

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

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

      Steve