Forum Discussion

kjsddf11's avatar
kjsddf11
New Member
2 years ago
Solved

Need help with matrix %

Greetings Power BI community!

 

I need to replicate the Excel matrix shown in the snippet below, in Power BI matrix. I've tried using a combination of formulas in Power BI, but the output isn't the same as in Excel. Could any expert please help me with this? I'm new to Power BI and eager to learn. Thanks in advance!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kjsddf11 ,
    Thanks for ryan_mayu  reply, here's how it worked for me
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create a column

    Column = 
var _equal = 
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Name]),
        'Table'[Name] = 'Table'[Name2]
    )
)
RETURN
[Value]/_equal

    Final output

    Best regards,

    Albert He

     

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

     

     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    kjsddf11 

    you can do the data transform(unpivot table) in pq

    then create a measure

    Measure = sum('Table'[Value])/CALCULATE(sum('Table'[Value]),all('Table'),'Table'[name]=max('Table'[name])&&'Table'[Attribute]=max('Table'[name]))
     
    pls see the attachment below
  • kjsddf11's avatar
    kjsddf11
    New Member
    2 years ago

    ryan_mayu it did not work sadly. The primary data isn't actually pivoted; I only formatted it as a pivot table in Excel for illustration purposes. The main dataset contains thousands of individual records for each brand for each month. Is there any other way to get the percentage i want?

      • kjsddf11's avatar
        kjsddf11
        New Member
        2 years ago

        Hi ryan_mayu , i can't create a sample data as it has so much to replicate. It will be a exhausting task. I am not sure, how to handle this!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kjsddf11 ,
    Thanks for ryan_mayu  reply, here's how it worked for me
    Here some steps that I want to share, you can check them if they suitable for your requirement.
    Here is my test data:

    Create a column

    Column = 
var _equal = 
CALCULATE(
    MAX('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[Name]),
        'Table'[Name] = 'Table'[Name2]
    )
)
RETURN
[Value]/_equal

    Final output

    Best regards,

    Albert He

     

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