Percentage based on a group

[Elmoataz]

Hello, I'm trying to create a column that shows the percentage based on the other columns. For example; in the picture below I got the percentage of each of the materials based on the Order number using the Amount column. So for the first Order Number (22), I got the percentage 50% for Boxes, 25% for Tables, and 25% for Chairs, so that's 100% total based on the Order Number column and then the other Order Number are the same. Thank you 

 

[Elmoataz]

@Jihwan_Kim , Him Kim, now I have an issue. When I filter out anything, the percentages stay the same. For example; for order number 45, when we filter out the Boxes, we will have 1000 Amount for the chairs and 1000 Amount for the tables, and the percentages should be 50% and 50%. But when I filter out the Boxes, the percentages stay the same as 25% and 25% as I didn't filter anything. Is there a way to fix this issue? Thank you so much for all your help.

[Jihwan_Kim]

Hi,

Thank you for your message.

Could you please change ALL to ALLSELECTED in the measure, and check whether it suits your requirement?

ALLSELECTED – DAX Guide

[Elmoataz]

@Jihwan_Kim Thank you so much. I have one more question. How can I get the average instead of the percentage? 

[Jihwan_Kim]

Hi,

Thank you for your message.

Please check the attached file.

Thanks.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Percentage measure: =
IF (
    HASONEVALUE ( 'Order Number'[Order number] ),
    DIVIDE (
        [Amount measure:],
        CALCULATE ( [Amount measure:], ALL ( Material[Materials] ) )
    )
)