Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I am using a formula called "New Price" to calculate item prices at various quantity breaks (discount for buying an item in bulk).
The issue is that sometimes my formula returns a higher price for a greater qty break (for the same item) than the price for a smaller qty break. I would like to filter out any "New Price" which doesn't make sense.
So if the price at qty break 5 is higher than the price at qty break 1 filter out the qty 5 price.
The first 3 columns in this table visual are dataset columns and the fourth is a DAX measure.
Item | Qty Break | Proposed Price | New Price |
A-123 | 1 | 2.43 | 5.38 |
A-123 | 2 | 6.88 | 6.88 |
A-123 | 3 | 6.38 | 6.38 |
A-123 | 4 | 5.38 | 5.38 |
A-123 | 5 | 4.98 | 5.91 |
A-123 | 6 | 4.98 | 5.91 |
A-123 | 7 | 4.98 | 5.91 |
A-123 | 8 | 4.98 | 5.91 |
A-123 | 9 | 4.98 | 5.91 |
A-123 | 10 | 4.98 | 4.98 |
A-123 | 11 | 4.98 | 4.98 |
A-123 | 12 | 4.98 | 4.98 |
A-123 | 13 | 4.98 | 4.98 |
A-123 | 14 | 4.98 | 4.98 |
A-123 | 15 | 4.98 | 4.98 |
A-123 | 16 | 4.98 | 4.98 |
A-123 | 17 | 4.98 | 4.98 |
A-123 | 18 | 4.98 | 4.98 |
A-123 | 19 | 4.98 | 4.98 |
A-123 | 20 | 4.98 | 4.98 |
How do I do this?
Thanks in advance!
Solved! Go to Solution.
Hi @b2wise ,
Sorry I misunderstood it. Modify the formula to:
Check =
IF (
[New Price]
<= MINX (
FILTER (
ALL ( 'Table' ),
NOT ( 'Table'[Qty Break] IN { 13, 14, 15, 16, 17, 18, 19 } )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
),
[New Price]
),
1,
0
)
I change the data and the Qty Break 20 still display as expected.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yanjiang-msft This works!! Thanks so much!
My error was writing the qty break filter with max (and not using IN)
Thanks again!
@v-yanjiang-msft Perfect! Can I trouble you to help me tweak this formula so it excludes certain qty breaks entirely?
For example, I want to ignore qty breaks 13 thru 19 in all situations so even if their price is lower than qty break 20 still show me the "New Price" for qty break 20.
Hi @b2wise ,
If you want the value from break 13 to break 19 always display anyway, modify the formula like this:
Check =
IF (
MAX ( 'Table'[Qty Break] ) >= 13
&& MAX ( 'Table'[Qty Break] ) <= 19,
1,
IF (
[New Price]
= MINX (
FILTER (
ALL ( 'Table' ),
'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
),
[New Price]
),
1,
0
)
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-kalyj-msft Sorry I wasn't clear.
I want MINX to find the minimum price at <=Qty Break but to ignore prices of 13 through 19. In other words, when evaluating at qty break 20 check to see if there was a lower price at "New Price" of 1 thru 12 only.
This is what I tried writing but it's not working.
IF(
[New Price]
= MINX (
FILTER (
ALL ( 'Table' ),
'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break]) && MAX('Table'[Qty Break])<> 11 && MAX('Table'[Qty Break])<> 13 && MAX('Table'[Qty Break])<> 14 && MAX('Table'[Qty Break])<> 15 &&
MAX('Table'[Qty Break])<> 16 && MAX('Table'[Qty Break])<> 17 && MAX('Table'[Qty Break])<> 18 && MAX('Table'[Qty Break])<> 19
),
[New Price]
),
1,
0
)
Hi @b2wise ,
Sorry I misunderstood it. Modify the formula to:
Check =
IF (
[New Price]
<= MINX (
FILTER (
ALL ( 'Table' ),
NOT ( 'Table'[Qty Break] IN { 13, 14, 15, 16, 17, 18, 19 } )
&& 'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
),
[New Price]
),
1,
0
)
I change the data and the Qty Break 20 still display as expected.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @b2wise ,
According to your description, here's my solution, create a measure.
Check =
IF (
[New Price]
= MINX (
FILTER (
ALL ( 'Table' ),
'Table'[Item] = MAX ( 'Table'[Item] )
&& 'Table'[Qty Break] <= MAX ( 'Table'[Qty Break] )
),
[New Price]
),
1,
0
)
Put the measure in the visual filter and select its value to 1.
After apply filter, get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
First step would be to show your measure formula.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
89 | |
35 | |
32 |
User | Count |
---|---|
153 | |
101 | |
82 | |
63 | |
52 |