Help with dax formula to normalize order values

[ase]

Hello, 

I have a problem to write a DAX formula which normalizes invoice data: 

I have data on the products that were ordered and the products that were received instead ( when the substitute is empty, it means that there was no substitute. 

Can someone help me with a Dax formula, which shows the most substituted products in a normalized way. (like under the yellow bar)

Thank you very much in advance!

Kind regards,

B.

[ase]

Hi @PaulOlding 

The idea was to show that in XX% of the cases, apples were substituted with another product.
The rationale for normalization part comes from the fact that for example we might have ordered 10 apples and 9 of them might have been substituted, and at the same time we might have ordered 1000 oranges, with 350 substituted, so I need to factor for the amount of the products. 

Unfortunately your solution only gives the number of substitutuions, but not the normalized ratio between ordered apples and times they were substituted.

Kind regards,

B.

[PaulOlding]

Hi @ase 

The 'Substituted Percent' measure gives the ratio.

[PaulOlding · Accepted solution]

Hi @ase 

I'm not totally sure what you mean by normalized, but here's some measures to help you out.  These give you numbers, so can be used in charts.

Order Count = COUNTROWS('Table')

Substituted Order Count = SUM('Table'[Is substituted])

Substituted Percent = DIVIDE([Substituted Order Count], [Order Count])

if you want the specific text in your example then we can have measures to build that text up.

Text 1 = 
VAR _Product = SELECTEDVALUE('Table'[Ordered product], "Multiple products")
VAR _Result = 
_Product & " " & [Order Count] & " orders total, " & [Substituted Order Count] & " substituted"
RETURN
    _Result

Text 2 = 
VAR _Product = SELECTEDVALUE('Table'[Ordered product], "Multiple products")
VAR _Result = 
_Product & " " & FORMAT([Substituted Percent] * 100, "#,0") & "% substituted"
RETURN
    _Result