Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

@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

 
 

 

Hi @Anonymous ,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.