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.
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
Solved! Go to Solution.
Thanks Fowmy for your quick answer.
I tried to measure but still have the same issue. the results is showing blank
deo you know why?
thanks
imbrg
Hi,
You may download my PBI file from here.
Hope this helps.
@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.
@Anonymous
please try this
Measure =
VAR tbl=SUMMARIZE('Table','Table'[orderid],"totalamount",sum('Table'[amount]))
return countrows(FILTER(tbl,[totalamount]>5000))
Proud to be a Super User!
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
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks Fowmy for your quick answer.
I tried to measure but still have the same issue. the results is showing blank
deo you know why?
thanks
imbrg
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |