Forum Discussion

TotallyRow's avatar
TotallyRow
Regular Visitor
1 year ago
Solved

Table/ Chart based on entries in the table - Power Bi

Hi,

 

I'm wondering if someone can help me, please. I've had a search but don't really know what to look for.

I have the table below [A]  (This is a Snippet here are 4 more columns and 35 more rows)

 

My end goal is to create 2 stacked column graphs, one to show how many letters of G, R, W, and L (y-axis) there are per Name(x-axis) and one per Q(x-axis)


I can do this if I can create and populate tables [B] and [C], (inside the table it will show the number of times the letters of G, R, W, and L for each of Name/Q)

 

My question is how can I create and populate tables [B] and [C] using 

  or is there another better way I can create those graphs 

 

Thanks for reading

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi TotallyRow ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours. Click “transform data” to enter the power query editor, then select the Name column, click “Unpivot other columns”.

    (2) We can create measures.

    Measure =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Name] = MAX ( 'Table'[Name] )
            && [Value] = MAX ( 'Table'[Value] )
    )
)

    Measure 2 =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Attribute] = MAX ( 'Table'[Attribute] )
            && [Value] = MAX ( 'Table'[Value] )
    )
)

    (3) Then the result is as follows.

     

     

    Best Regards,

    Neeko Tang

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TotallyRow ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours. Click “transform data” to enter the power query editor, then select the Name column, click “Unpivot other columns”.

    (2) We can create measures.

    Measure =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Name] = MAX ( 'Table'[Name] )
            && [Value] = MAX ( 'Table'[Value] )
    )
)

    Measure 2 =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Attribute] = MAX ( 'Table'[Attribute] )
            && [Value] = MAX ( 'Table'[Value] )
    )
)

    (3) Then the result is as follows.

     

     

    Best Regards,

    Neeko Tang

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    After using the "Unpivoting Other Columns" feature, you may just drag this measure to your matrix visual

    Measure = countrows(Data)

    Hope this helps.

  • TotallyRow's avatar
    TotallyRow
    Regular Visitor
    1 year ago

    Hi Anonymous 

    Thank you so much!!! Your solution worked as expected, I did not expect such a great step-by-step approach.

     

    Thanks Again!