topic Re: Help with dax formula to normalize order values in DAX Commands and Tips

Help with dax formula to normalize order values

ase — Tue, 12 Jul 2022 15:04:55 GMT

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,

Re: Help with dax formula to normalize order values

PaulOlding — Tue, 12 Jul 2022 16:22:06 GMT

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

Re: Help with dax formula to normalize order values

ase — Wed, 13 Jul 2022 08:40:59 GMT

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,

Re: Help with dax formula to normalize order values

PaulOlding — Wed, 13 Jul 2022 09:08:15 GMT

Hi ase

The 'Substituted Percent' measure gives the ratio.