cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Value always "on top" based on filters

Hi, 

I am trying to create a measure or column that can help me achieve. Please see below for my attempt at an explanation of what I want. 

This is what the raw data looks like:

Booking ID Revenue Offer IDOffer Type
1          1,0001Seasonal
1          1,0001Seasonal
2          2,0001Seasonal
3          3,0001Seasonal
1          1,0002Flash Sale
1          1,0002Flash Sale
2          2,0002Flash Sale
3          3,0002Flash Sale
3          3,0003Hot Deal

 

In the front end, if there are no filters applied, I want it to look like this:

Booking ID Revenue Offer IDOffer Type
1          1,0001Seasonal
1                 -  1Seasonal
1                 -  2Flash Sale
1                 -  2Flash Sale
2          2,0001Seasonal
2                 -  2Flash Sale
3          3,0001Seasonal
3                 -  2Flash Sale
3                 -  3Hot Deal
           6,000  

Even though mulitple offers were used in the same booking, I only want to sum the revenue once. (It really doesn't matter which offer it would be associated with). This alone can be achieved with a maxx(filter(, but there's more to it that makes it complicated. 

 

If I filtered using Booking ID I'd want to see:

Booking ID Revenue Offer IDOffer Type
1          1,0001Seasonal
1                 -  1Seasonal
1                 -  2Flash Sale
1                 -  2Flash Sale
           1,000  

If only looking at booking 1, the revenue would be 1,000. (Once again, this would be fine with the soluation mentioned above). 

 

Let's say I want to filter the offer ID:

Booking ID Revenue Offer IDOffer Type
1          1,0002Flash Sale
1                 -  2Flash Sale
2          2,0002Flash Sale
3          3,0002Flash Sale
 

6,000

  

Here, I am looking at offer #2. But if I was using the maxx(filter(, or another combination of that, the revenue would only be showing on the first offer (offer 1). 

 

My main goal here is to only show the revenue once (on top, or on bottom), but depending on what is filtered, would determine where the revenue would show. (doesn't matter where, I only want it to show once). 

 

If mulitple offer IDs are selected, I would want to see it as:

Booking ID Revenue Offer IDOffer Type
1          1,0002Flash Sale
1                 -  2Flash Sale
2          2,0002Flash Sale
3          3,0002Flash Sale
3                 -  3Hot Deal
           6,000  

 

If filtered with booking & offer I'd want to see it as:

Booking ID Revenue Offer IDOffer Type
1          1,0002Flash Sale
1                 -  2Flash Sale
           1,000  

 

Is there any way to do this? I can accomplish it using a isfiltered with different revenue columns based on the different filters, but then it doesn't show a revenue if more than one filter is applied, and sometimes 2-3 filters can be applied. It's important to not double count revenue as we want accurate totals based on the numbers. 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Value always "on top" based on filters

Hi @sriecke ,

 

Please update the measure 2 as below.

Measure 2 =
VAR bookingid =
    VALUES ( 'Booking ID_'[Booking ID] )
VAR offerid =
    VALUES ( 'Offer ID'[Offer ID] )
VAR mindb =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Booking ID] IN bookingid ),
        VALUES ( 'Table'[Booking ID] )
    )
VAR minofid =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Offer ID] IN offerid ),
        VALUES ( 'Table'[Booking ID] )
    )
VAR minboth =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        VALUES ( 'Table'[Booking ID] ),
        VALUES ( 'Table'[Offer ID] )
    )
VAR indexc =
    IF (
        ISFILTERED ( 'Booking ID_'[Booking ID] ) && ISFILTERED ( 'Offer ID'[Offer ID] ),
        minboth,
        IF (
            ISFILTERED ( 'Booking ID_'[Booking ID] )
                && NOT ( ISFILTERED ( 'Offer ID'[Offer ID] ) ),
            mindb,
            IF (
                ISFILTERED ( 'Offer ID'[Offer ID] )
                    && NOT ( ISFILTERED ( 'Booking ID_'[Booking ID] ) ),
                minofid,
                IF (
                    NOT (
                        AND (
                            ISFILTERED ( 'Booking ID_'[Booking ID] ),
                            ISFILTERED ( 'Offer ID'[Offer ID] )
                        )
                    ),
                    mindb
                )
            )
        )
    )
VAR resu =
    IF ( MAX ( 'Table'[Index] ) = indexc, SUM ( 'Table'[ Revenue ] ), 0 )
RETURN
    SUMX (
        FILTER (
            'Table',
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        resu
    )

2.PNG

 

Attached the pbix as well.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Value always "on top" based on filters

Hi @sriecke ,

 

I have created a sample for your reference, please check the following steps as below.

1. Sort your table and insert an index column in power query.

2. Create two calculated tables as below.

Booking ID_ = DISTINCT('Table'[Booking ID])
Offer ID = DISTINCT('Table'[Offer ID])

 3. Then we can create two measures to make it out.

Measure 2 = 
VAR bookingid =
    VALUES ( 'Booking ID_'[Booking ID] )
VAR offerid =
    VALUES ( 'Offer ID'[Offer ID] )
VAR mindb =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Booking ID] IN bookingid ),
        VALUES ( 'Table'[Booking ID] )
    )
VAR mindof =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Offer ID] IN offerid ),
        VALUES ( 'Table'[Offer ID] )
    )
VAR minboth =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        VALUES ( 'Table'[Booking ID] ),
        VALUES ( 'Table'[Offer ID] )
    )
VAR indexc =
    IF (
        ISFILTERED ( 'Booking ID_'[Booking ID] ) && ISFILTERED ( 'Offer ID'[Offer ID] ),
        minboth,
        IF (
            ISFILTERED ( 'Booking ID_'[Booking ID] )
                && NOT ( ISFILTERED ( 'Offer ID'[Offer ID] ) ),
            mindb,
            IF (
                ISFILTERED ( 'Offer ID'[Offer ID] )
                    && NOT ( ISFILTERED ( 'Booking ID_'[Booking ID] ) ),
                mindof
            )
        )
    )
VAR resu =
    IF ( MAX ( 'Table'[Index] ) = indexc, SUM ( 'Table'[ Revenue ]), 0 )
RETURN
    SUMX (
        FILTER (
            'Table',
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        resu
    )
Measure = SUMX('Table',[Measure 2])

Capture.PNG

 

For more details ,please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Frequent Visitor

Re: Value always "on top" based on filters

@v-frfei-msft , this doesn't work the way I want it to. If no filters are selected, the measure is showing 0, when the total should be $6,000. 

 

Below screenshot is with no filters, but no revenue shows in the measure, when I would want it to. 

pbi.png

The second screenshot is with only one filter, and the measure is only applying to the first one. I want it to show for each booking. Since the offer was used in all bookings, the total would be $6,000 in revenue. Here the revenue for the measure is only showing for booking 1, not the other 2. 

pbi2.png

 
 

 

Highlighted
Community Support
Community Support

Re: Value always "on top" based on filters

Hi @sriecke ,

 

Please update the measure 2 as below.

Measure 2 =
VAR bookingid =
    VALUES ( 'Booking ID_'[Booking ID] )
VAR offerid =
    VALUES ( 'Offer ID'[Offer ID] )
VAR mindb =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Booking ID] IN bookingid ),
        VALUES ( 'Table'[Booking ID] )
    )
VAR minofid =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Offer ID] IN offerid ),
        VALUES ( 'Table'[Booking ID] )
    )
VAR minboth =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        VALUES ( 'Table'[Booking ID] ),
        VALUES ( 'Table'[Offer ID] )
    )
VAR indexc =
    IF (
        ISFILTERED ( 'Booking ID_'[Booking ID] ) && ISFILTERED ( 'Offer ID'[Offer ID] ),
        minboth,
        IF (
            ISFILTERED ( 'Booking ID_'[Booking ID] )
                && NOT ( ISFILTERED ( 'Offer ID'[Offer ID] ) ),
            mindb,
            IF (
                ISFILTERED ( 'Offer ID'[Offer ID] )
                    && NOT ( ISFILTERED ( 'Booking ID_'[Booking ID] ) ),
                minofid,
                IF (
                    NOT (
                        AND (
                            ISFILTERED ( 'Booking ID_'[Booking ID] ),
                            ISFILTERED ( 'Offer ID'[Offer ID] )
                        )
                    ),
                    mindb
                )
            )
        )
    )
VAR resu =
    IF ( MAX ( 'Table'[Index] ) = indexc, SUM ( 'Table'[ Revenue ] ), 0 )
RETURN
    SUMX (
        FILTER (
            'Table',
            'Table'[Booking ID] IN bookingid
                && 'Table'[Offer ID] IN offerid
        ),
        resu
    )

2.PNG

 

Attached the pbix as well.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors