Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ID | Offer Type |
1 | 1,000 | 1 | Seasonal |
1 | 1,000 | 1 | Seasonal |
2 | 2,000 | 1 | Seasonal |
3 | 3,000 | 1 | Seasonal |
1 | 1,000 | 2 | Flash Sale |
1 | 1,000 | 2 | Flash Sale |
2 | 2,000 | 2 | Flash Sale |
3 | 3,000 | 2 | Flash Sale |
3 | 3,000 | 3 | Hot Deal |
In the front end, if there are no filters applied, I want it to look like this:
Booking ID | Revenue | Offer ID | Offer Type |
1 | 1,000 | 1 | Seasonal |
1 | - | 1 | Seasonal |
1 | - | 2 | Flash Sale |
1 | - | 2 | Flash Sale |
2 | 2,000 | 1 | Seasonal |
2 | - | 2 | Flash Sale |
3 | 3,000 | 1 | Seasonal |
3 | - | 2 | Flash Sale |
3 | - | 3 | Hot 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 ID | Offer Type |
1 | 1,000 | 1 | Seasonal |
1 | - | 1 | Seasonal |
1 | - | 2 | Flash Sale |
1 | - | 2 | Flash 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 ID | Offer Type |
1 | 1,000 | 2 | Flash Sale |
1 | - | 2 | Flash Sale |
2 | 2,000 | 2 | Flash Sale |
3 | 3,000 | 2 | Flash 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 ID | Offer Type |
1 | 1,000 | 2 | Flash Sale |
1 | - | 2 | Flash Sale |
2 | 2,000 | 2 | Flash Sale |
3 | 3,000 | 2 | Flash Sale |
3 | - | 3 | Hot Deal |
6,000 |
If filtered with booking & offer I'd want to see it as:
Booking ID | Revenue | Offer ID | Offer Type |
1 | 1,000 | 2 | Flash Sale |
1 | - | 2 | Flash 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!
Solved! Go to 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
)
Attached the pbix as well.
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])
For more details ,please check the pbix as attached.
@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.
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.
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
)
Attached the pbix as well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |