Summarize with Calculate

[Anonymous]

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

[Anonymous · Accepted 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

[Greg_Deckler]

@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.

[ryan_mayu]

@Anonymous 

please try this

Measure = 
VAR tbl=SUMMARIZE('Table','Table'[orderid],"totalamount",sum('Table'[amount]))
return countrows(FILTER(tbl,[totalamount]>5000))

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted 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

[Fowmy]

@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 🙂

YouTube, LinkedIn

[Fowmy]

@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 🙂

YouTube, LinkedIn