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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jwin2424
Resolver I
Resolver I

Group order lines then apply filters

Hello, 

I am struggling to get the same answer as the visual I created. I have data that shows order #s broken out by lines. When I create the filter to show orders greater than 250,000, it is filtering order lines by 250,000 and not the order itself. Examples

Order #Line #Amount
1122150000
11222150000
11223200000
1122450000
2233125000
2233225000


Order 1122 is over 250,000 and order 2233 is not. However, when I filter by order #'s greater than 250,000, it will show none. 

I finally went one step at a time and got to this

jwin2424_2-1708971708509.png


I made a visual with the same filters as the measure I created. The numbers come out the same. 

I then go one step furter and ask it to group the table by order # and only shows orders over 250,000 and I get this

jwin2424_3-1708971988567.png


Once I try and add a measure to group the orders by 250,000, the numbers dont match. The visual is correct and I used the filters on the right and just threw the orders in the rows. In the filter on the visual, I said orders greater than 250000. 


I can't for the life of me figure out what I am doing wrong. 

Any help is appreciated. 

Thanks!

 



1 ACCEPTED SOLUTION

hi @jwin2424 

 

If its a single table and I have used the data which you shared and the numbers match.

 

I have uploaded pbix file here, please let me know once you have copied.

https://drive.google.com/file/d/1s5tjDp40tp__pTJnXc7tTfseMAIg2_F-/view?usp=sharing

 

The Left table is with filters applied with a simple SUM amount measure.

talespin_0-1709268030092.png

 

On the right is Table with measure.

talespin_1-1709268116413.png

 

View solution in original post

18 REPLIES 18
talespin
Solution Sage
Solution Sage

hi @jwin2424 ,

 

Please use this measure, you need to add all the filters in FILTER as well as add these columns in SUMMARIZE.

 
In screenshot, table on right is my source data.
 
AmountFilter =
VAR _SummTbl = ADDCOLUMNS(
                            SUMMARIZE( FILTER(Orders, Orders[OnATP] = "Yes" && Filter2 && Filter3.....), Orders[OredrNo], Orders[OnATP], Add filter columns here. ),
                            "@SumAmt", CALCULATE(SUM(Orders[Amount]))
)

RETURN SUMX( FILTER(_SummTbl, [@SumAmt] > 250000), [@SumAmt])
 
talespin_2-1709039494701.png

 

THANK YOU!!! 

That worked. However, can you help with ONE more thing?

Here is the DAX measure:

Orders Over 250k =
VAR _SummTbl =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER('Backlog Reports', 'Backlog Reports'[On ATP] = "No" && 'Backlog Reports'[PF] <> 2505 && 'Backlog Reports'[Has Fixed Holds] = "Has Fixed Hold" && ('Backlog Reports'[Schedule Category] = "Q2+ 24" || 'Backlog Reports'[Schedule Category] =" Unscheduled")), 'Backlog Reports'[Order #], 'Backlog Reports'[On ATP], 'Backlog Reports'[Has Fixed Holds], 'Backlog Reports'[Schedule Category] ),
                            "@SumAmt", CALCULATE(SUM('Backlog Reports'[Ext Price-USD]))
)

RETURN SUMX( FILTER(_SummTbl, [@SumAmt] > 250000), [@SumAmt])


However, I can't seem to get the OR statement to apply. I need to filter "Q2+ 24" OR " Unscheduled" and it seems to be only applying the Q2+ 24 filter. 

Thanks!

hi @jwin2424 

 

I see a space before Unscheduled, please confirm if that is correct.

" Unscheduled"

 

I see that you have not included 'Backlog Reports'[PF] in Summarize which means that it will not be applied as filter when calculating this CALCULATE(SUM('Backlog Reports'[Ext Price-USD]))

The " Unscheduled" indeed has a space before the word. For sake of simplifying things, I wanted to show you just Q2+ 24 for this example: 

jwin2424_0-1709138555390.png


When I leave out PF, I get the right number as the visual with the exact same filters. 

jwin2424_1-1709138589975.png


When I add in PF, the numbers in the measure change. 

I have also notived another weird thing with the power bi visual and the filters. 

I have the fields Customer and Order # in the rows section of the visual with the power bi "Filters on this visual." When I remove Order # from the rows, the total changes. 

jwin2424_2-1709138882964.png


The total $5,581,139 is the total I get when I create a pivot in excel with these same filters. I am not sure why adding or removing the order # is changing the result in the visual with the filters on the right. It doesn't affect the measure when i remove the order # (which is what I would expect). 

hi @jwin2424 

 

The problem lies with Measure, since PF has a <> operator, it means all values other than 2505, since one order no can have multiple line items and may be has different PF values, SUMMARIZE and @Sum will calculate sum at grup level and this group includes all the columns that are part of Summarize, so when you add PF total is incorrect.

 

Please try below measure and let me know. Also I am using single table, if you have multiple tables then it may require modification. Providing explanation with measure in screenshot.

 
AmountFilter =
VAR _SummTbl = ADDCOLUMNS(
                            -- Group data by Order#
                            SUMMARIZE( Orders, Orders[OredrNo] ),
                            "@SumAmt",
                            --Here I am adding SUM of amount at Order level(I have only used OrderNo in SUMMARIZE so that @SumAMt is calculated at Order level rather than order line item level.)
                            --I am passing order# to CALCULATE along with all the filters.
                            VAR _OrderNo = [OredrNo]                            
                            RETURN CALCULATE(SUM(Orders[Amount]), REMOVEFILTERS(), Orders[OredrNo] = _OrderNo && Orders[OnATP] = "Yes")
)

--This will remove all orders where SumAmt is <= 350000
 --If you place it in card it will give you sum of all the orders, but if you place it in a table with Order#, it will give you sum by Order#
RETURN SUMX( FILTER(_SummTbl, [@SumAmt] > 350000), [@SumAmt])
 
 
talespin_1-1709184735287.png

 


 

This is driving me insane. I tried the above, and it isnt working. I have one table and no relationships. Let's see if this helps explain. I have created another single table with less data. Filters are same. You can see in both power bi and excel, when the filters are applied, the totals are the same. When I use the formula, I do not get the same answer. 

jwin2424_0-1709229071668.png

 

When I add in the OR filter, nothing changes in the results. 

jwin2424_1-1709229176869.png

And to show you what happens when I try the new formula you provided

jwin2424_2-1709229572966.png

 

It will not let me paste in my table (which is only 8500 characters) because it says I exceed the 20,0000 character limit for a post. I am going to try and post on a seperate reply.

EDIT: Maybe this will work

https://docs.google.com/spreadsheets/d/14-DlkOumQKKPTRgKVCWAtnfuGP62vUFZ7pESLZ9iOeM/edit?usp=sharing
I also created a measure for On ATP as follows: 

On ATP =
IF(Sheet1[ATP Desc] = BLANK(), "No", "Yes")

 

 

hi @jwin2424 

 

If its a single table and I have used the data which you shared and the numbers match.

 

I have uploaded pbix file here, please let me know once you have copied.

https://drive.google.com/file/d/1s5tjDp40tp__pTJnXc7tTfseMAIg2_F-/view?usp=sharing

 

The Left table is with filters applied with a simple SUM amount measure.

talespin_0-1709268030092.png

 

On the right is Table with measure.

talespin_1-1709268116413.png

 

I included the OR filter as well, and this worked. I did take out the REMOVEFILTERS () because I did have a date slicer on the page. 

Thank you SO much for helping me! This will make a huge imapct for my report. 

hi @jwin2424 

 

You're welcome.

jwin2424
Resolver I
Resolver I


Hopefully this works. You can see the measure itself does not reflect the visual with the same filters applied

Hi @jwin2424 

 

It's asking for my password at your company which I'm sure isn't what you wanted.

 

You'll have to upload your pbix file to OneDrive, GoogleDrive, WeTransfer, or Dropbox and copy the link.  Remember to give necessary permissions if required.

 

 

Uuuhg looks like my company blocked uploads from those sites. I used to be able to do it from one drive, but for some reason it asked you for a PW. It hasn't done that in the past. 

Best I can do is just the visual

jwin2424_0-1708986983122.png


The measure results in the above total. 

jwin2424_1-1708987024830.png

 

The filters on the visual result in this total. I just want a measure that replicates the filters on the right. 

Not sure why it is capturing these orders

jwin2424_0-1708987410213.png

 

@jwin2424 

 

Would it help to change your variable declaration to something like this?

 

VAR FilteredOrders =
	SELECTCOLUMNS(
		FILTER(
			ADDCOLUMNS(
				VALUES( 'Sheet1'[Order # 2] ),
				"_sales",
					CALCULATE( SUM( 'Sheet1'[Ext Price-USD] ) )
			),
			[_sales] > 250000
		),
		"Order # 2",
			[Order # 2]
	)

 

 

Unfortunately, this yielded the same result as mine. 

gmsamborn
Super User
Super User

Hi @jwin2424 

 

With a measure like this, you should be able to filter orders over 250K.

Filter Orders over 250K = 
VAR _OrderSales =
    CALCULATE(
        SUM( 'Table'[Amount] ),
        ALLEXCEPT(
            'Table',
            'Table'[Order #]
        )
    )
VAR _Result =
    IF(
        _OrderSales > 250000,
        1,
        0
    )
RETURN
    _Result

 

I hope I understood correctly.

 

Group order lines then apply filters.pbix

 

Unfortunately that does not work. I need a measure that fulfills these filters. 

jwin2424_1-1708978352176.png

 



Where I am struggling is grouping the data by the order # so that the measure applies the filters to order #s over 250000. Right now, it is calculating row by row, and the order #'s are broken down by line #'s. I need the order # to be grouped into one distinct order # THEN apply the remaining filters. 

 

Hi @jwin2424 

 

Is there any way you could come up with a pbix with sample data that covers this issue including the columns to be used as filters ?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.