How to use the total value for a measure in a calculation in a table

[Anonymous]

So I have some order data and am analyzing the frequency at which 2 products appear on the same order. I aggregated the order table and matched every product with itself in a new column called, "OnSameTicket." Which is basically just ProductIDs. I have a measure NumOrders which is the DISTINCTCOUNT of OrderIDs,  Matches which is the count of OnSameTicket for each ID, and PercentMatch which is Matches divided by NumOrders. My issue is that PercentMatch is dividing Matches by the NumOrders of that particular row in the column, as demonstrated by the included table. I would like PercentMatch to be the Matches divided by the Total of NumOrders(the number displayed on the card on the right.) This way it will show the percentage of receipts containing the ProductID selected in the slicer that also contain the ProductID in OnSameTicket. Any advice would be greatly appreciated.

[v-lionel-msft]

Hi @Anonymous ,

Was your problem solved? If yes, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,
Lionel Chen

[danextian]

Hi @Anonymous ,

You can try something like below as a calculated column to indicate whether a ticket has both the products

Product A & B in the same ticket =
VAR __COUNT =
    CALCULATE (
        COUNTA ( Table[Ticket] ),
        //Count only tickets that have both product A and B
        FILTER (
            ALL ( Table ),
            Table[Ticket] = EARLIER ( Table[Ticket] )
                && ( Table[Product] = "Product A"
                || Table[Product] = "Product B" )
        )
    )
RETURN
    __COUNT >= 1
    && __COUNT <> BLANK ()

And then create a measure to get the percentage

Percentage =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( Table[Ticket] ),
        'Table[Product A & B in the same ticket]' = TRUE ()
    ),
    DISTINCTCOUNT ( Table[Ticket] )
)

Otherwise, please post a sample data that we can copy-paste (not a picture).

[v-lionel-msft]

Hi @Anonymous ,

" I would like PercentMatch to be the Matches divided by the Total of NumOrders(the number displayed on the card on the right.)"

Maybe you can try this:

PercentMatch =
VAR x = 
CALCULATE(
    DISTINCTCOUNT([OrderIDs]),
    ALL(table)
)
RETURN
DIVIDE(
    x,
    Matches,
    BLANK()
)

Or this:

PercentMatch=
CALCULATE(
    DIVIDE(
    [NumOrders],
    [Matches],
    BLANK()
    ),
    ALL(table)
)

All() function 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.