Forum Discussion

sara_alonso's avatar
sara_alonso
Icon for Helper I rankHelper I
6 years ago
Solved

Filter column by minimum revenue

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
sara_alonso
Icon for Helper I rankHelper I
6 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    sara_alonso
    Icon for Helper I rankHelper I
    6 years ago

    Anonymous Sorry for the late reply, this worked perfectly, many thanks!!!