Forum Discussion

ase's avatar
ase
Frequent Visitor
4 years ago
Solved

Help with dax formula to normalize order values

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.

 

  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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

     

     

     

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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

     

     

     

  • ase's avatar
    ase
    Frequent Visitor
    4 years ago

    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.