Measure with three outcomes with slicer

[Anonymous]

Hi, I have the following table:

I need help with a DAX function that combines Paid, Free and Total (Paid+Free).
So when I put in Quantity in a matrix visual I can be able to use a slicer to pick between paid, free and total.
Is this possible to do?
Appriciate any help!

[tamerj1 · Accepted solution]

Hi @Anonymous 
Yes can be done.
You create a separate table for the required selections

The create a relationship

Then create your measure

QTY = 
if ( 
    SELECTEDVALUE ( Selection[Selection] ) = "Total",
    CALCULATE ( SUM ( Data[Quantity] ), REMOVEFILTERS ( Selection ) ),
    SUM ( Data[Quantity] )
)

[OwenAuger · Accepted solution]

Hi @Anonymous 

I would normally solve this sort of thing with the data model rather than DAX, in this case by creating an additional table looking like this (let's call it Ticket type filter😞

Ticket type filter	Ticket type
Paid	Paid
Free	Free
Total	Paid
Total	Free

This table could be related directly to your original table with a many:many relationship between the two Ticket type columns, but I would prefer to have a Ticket type dimension bridging the two. Note the bidirectional relationship between Ticket type filter and Ticket type:

Filters can then be applied to 'Ticket type filter'[Ticket type filter], and the advantage is that this will work for any measure based on the Tickets fact table.

Simple PBIX attached.

Regards,

Owen

[Anonymous]

@OwenAuger thank you! Seems to work great.

I discovered another problem regaring this issue:
I have an external source file (from excel) for competitors where I dont have the "ticket type"-column with Free and Paid. I only have the Total. 
I have to make an estimate, so lets say that 1% of the total tickets for each day are free, how can I include this in the logic that you guys helped me with?

[tamerj1 · Accepted solution]

@Anonymous 
No issues. Of course there would be no relationship. You can then use the following code

QTY = 
SWITCH (
    TRUE, 
    SELECTEDVALUE ( Selection[Selection] ) = "Free", SUM ( Data[Quantity] ) * 0.1,
    SELECTEDVALUE ( Selection[Selection] ) = "Paid", SUM ( Data[Quantity] ) * 0.9,
    SUM ( Data[Quantity] )
)

[Anonymous]

@tamerj1 That works perfect, thanks!

[OwenAuger · Accepted solution]

Hi @Anonymous 

I would normally solve this sort of thing with the data model rather than DAX, in this case by creating an additional table looking like this (let's call it Ticket type filter😞

Ticket type filter	Ticket type
Paid	Paid
Free	Free
Total	Paid
Total	Free

This table could be related directly to your original table with a many:many relationship between the two Ticket type columns, but I would prefer to have a Ticket type dimension bridging the two. Note the bidirectional relationship between Ticket type filter and Ticket type:

Filters can then be applied to 'Ticket type filter'[Ticket type filter], and the advantage is that this will work for any measure based on the Tickets fact table.

Simple PBIX attached.

Regards,

Owen

[tamerj1 · Accepted solution]

Hi @Anonymous 
Yes can be done.
You create a separate table for the required selections

The create a relationship

Then create your measure

QTY = 
if ( 
    SELECTEDVALUE ( Selection[Selection] ) = "Total",
    CALCULATE ( SUM ( Data[Quantity] ), REMOVEFILTERS ( Selection ) ),
    SUM ( Data[Quantity] )
)