Filter column by minimum revenue

[sara_alonso]

Hello, 

I have the following data of a store sales below (it is a simplification of my dataset). I would need to quantify how much of the total revenue of the store comes from tickets>200€, as a percentage of the total revenue. 

For that, I am trying to find out the revenue from tickets > 200€ but my formulas with Calculate and Filter aren't working. Could you please help me out?

Ticket number	Product	Product price	Ticket  total
1	Shirt	100	350
1	Shirt	100	350
1	Jeans	150	350
2	Shirt	100	250
2	Jeans	150	250
3	Belt	30	30

Many thanks!

[Anonymous]

Try this

Revenue= sumx(filter(table,table[ticket total]>200),table[ticket total]-table[product price])

Thanks
Pravin

[sara_alonso]

Many thanks for the suggestion @Anonymous , unfortunately the result is still not the correct one 😞 

[amitchandak]

@sara_alonso, Try first, if it gives the wrong GT then use second. The second one uses the first one.

Revenue= calculate(sum(Table[Ticket total]), filter(Table,table[Ticket total]>200))

Revenue2= sumx(summarize(Table,Table[Ticket total],table[Product],"Revenue",[Revenue]),[Revenue])

[sara_alonso]

Many thanks @amitchandak ,

The first one was what I was trying, but it doesn't work properly, as it sums several times the amount of the same ticket. When I try with the second formula you suggest, the result is the same. 

For example, in the case above, it sums 3 * 350, and 2 * 250, instead of just one per each ticket, so the final amount is incorrect. It needs to be grouped somehow so it only takes one amount per ticket. 

Any thoughts? Thanks again! 

[amitchandak]

@sara_alonso Ok can share you revenue formula

This single one should work. Just correct the multiplication in revenue and filter condition

Revenue2= sumx(summarize(filter(Table,Table[Ticket total],table[Product],"Revenue",sumx(Table,Table[price]*Table[Qty])),[Revenue]>200 ),[Revenue])

[sara_alonso]

Many thanks @amitchandak , 

When I type the first part of the formula, and I arrive to table product, it states that Filter can only have 2 arguments. 

Revenue2= sumx(summarize(filter(Table,Table[Ticket total],table[Product],

On the other side, what is this parameter? Table[Qty] Is it a new measure I should calculate?

[v-yiruan-msft · Accepted solution]

Hi @sara_alonso ,

You can try to create one measure as below:

Measure = 
VAR t =
    SUMMARIZE (
        'Tickets',
        'Tickets'[Ticket number],
        "TicketNum", ALLSELECTED ( 'Tickets'[Ticket number] ),
        "Tickettotal", MAX ( 'Tickets'[Ticket  total])
    )
RETURN
    DIVIDE (
        SUMX ( FILTER ( t, [Tickettotal] > 200 ), [Tickettotal] ),
        SUMX ( ALL ( 'Tickets' ), 'Tickets'[Product price] )
    )

Best Regards

Rena

[sara_alonso]

@v-yiruan-msft Sorry for the late reply, this worked perfectly, many thanks!!!

[amitchandak]

@sara_alonso 

Sorry my bad

Revenue2= sumx(filter(summarize(Table,Table[Ticket total],table[Product],"Revenue",sumx(Table,Table[price]*Table[Qty])),[Revenue]>200 ),[Revenue])

I am not sure on qty, Any product or number you want sum should be part of revenue.