Forum Discussion
Summarize with Calculate
Hello,
I have a header-detail type of table for Orders and details. An order can have many order lines
I am trying to get the number of orders with a total amount is greater than 5000. I am using the following formula but it returs blank
CALCULATE(DISTINCTCOUNT(Orders[Id_Order]), FILTER(SUMMARIZE(Orders, Orders[Id_Order],"Order Amount", SUM(Orders[TotalPriceConverted]))
, [Order Amount]>5000))
can you please support
thank you
Imbrg
7 Replies
Anonymous
Can you try this measure?Measure = COUNTROWS( FILTER( SUMMARIZE( Orders, Orders[Id_Order], "Order Amount",CALCULATE(SUM(Orders[TotalPriceConverted])) ), [Order Amount]>5000 ) )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
Anonymous
Check if you have over 5000 counts in the table and you mentioned Order Details but your formula only refers to Order (Header) table.
Can you share some sample data?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.Click on the Thumbs-Up icon on the right if you like this reply 🙂
Anonymous
please try this
Measure = VAR tbl=SUMMARIZE('Table','Table'[orderid],"totalamount",sum('Table'[amount])) return countrows(FILTER(tbl,[totalamount]>5000))Hello Fowmy,
I have a header-detail type of table for Orders and details. An order can have many order lines
I am trying, now, to calculate the number of order lines where the total amount at the Order level is greater than 5000.
Id_Order
ID_Order_Line
Amount
1
10
2500
1
20
3000
1
30
1000
2
15
4000
2
25
3600
3
35
450
In the table above I should have the number of order lines 5
Measure =
COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[Id_Order],
Orders[Id_Orderline],
"Order Amount",CALCULATE(SUM(Orders[TotalPriceConverted]))
),
[Order Amount]>5000
)
)
thank you for your support
Anonymous - Try using COUNTX or COUNTROWS instead. Like:
Measure =
VAR __Table = SUMMARIZE( ... )
RETURN
COUNTROWS(FILTER(__Table,<filter criteria>))
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
