Need help with matrix %

[kjsddf11]

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!

[Anonymous · Accepted solution]

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

[kjsddf11]

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

[ryan_mayu]

then pls provide the sample data

[kjsddf11]

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!

[ryan_mayu]

@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