cancel
Showing results for
Did you mean:
Post Prodigy

## Count the order number by Account

Hi, how to sum the number or orders per account?

If the number of order per account >5, then set it as 5.

I have created the below measures, but the sum = 6 which is incorrect.

`Total Orders by Account = IF(CALCULATE(DISTINCTCOUNT(Data[Order]), GROUPBY(Data,Data[Account]))>5,5,CALCULATE(DISTINCTCOUNT(Data[Order]), GROUPBY(Data,Data[Account])))`

Expected Outcome:

 Account # of Order A 5 B 3 C 2 Total 10

 Order Account O-123 A O-234 B O-345 A O-456 A O-567 B O-678 C O-789 A O-900 A O-1011 C O-1122 A O-1233 B

1 ACCEPTED SOLUTION
Community Champion

Hi, @PBI_newuser

Please check the below picture and the sample pbix file's link down below.

Number of Orders Measure =
VAR newtable =
SUMMARIZE (
'Table',
'Table'[Account],
"@orderscountcondition", IF ( COUNTROWS ( 'Table' ) >= 5, 5, COUNTROWS ( 'Table' ) )
)
RETURN
SUMX ( newtable, [@orderscountcondition] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

5 REPLIES 5
Community Champion

Hi, @PBI_newuser

Please check the below picture and the sample pbix file's link down below.

Number of Orders Measure =
VAR newtable =
SUMMARIZE (
'Table',
'Table'[Account],
"@orderscountcondition", IF ( COUNTROWS ( 'Table' ) >= 5, 5, COUNTROWS ( 'Table' ) )
)
RETURN
SUMX ( newtable, [@orderscountcondition] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Post Prodigy

Hi @Jihwan_Kim, I tried to modify your measure but it doesn't work. Sample here.

`Number of Orders Measure = VAR newtable = SUMMARIZE ( 'Table', 'Table'[Account], "@orderscountcondition", IF ( COUNTROWS ( 'Table' ) >= [Max Orders Per Account], [Max Orders Per Account], COUNTROWS ( 'Table' ) ) )RETURN SUMX ( newtable, [@orderscountcondition] )`
Can "Montdiff" calculate the month difference when users select Relatvie Date?
I noticed when I select Account=C, the Montdiff = 6 although i have selected "Last 12 months".
`Monthdiff = CALCULATE(DATEDIFF(MIN('Table'[Date]),Max('Table'[Date]),MONTH)+1,ALLSELECTED('Table'))`

`Max Orders Per Account = 6/12 * [Monthdiff]`
Community Champion

Hi, @PBI_newuser

I am not 100% sure that I understand what you are looking for, but please try the below.

Number of Orders =
VAR maxordersperaccount = [Max Orders Per Account]
VAR newtable =
SUMMARIZE (
'Table',
'Table'[Account],
"@orderscountcondition",
IF (
COUNTROWS ( 'Table' ) >= maxordersperaccount,
maxordersperaccount,
COUNTROWS ( 'Table' )
)
)
RETURN
SUMX ( newtable, [@orderscountcondition] )

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Post Prodigy

Hi @Jihwan_Kim , it works!! Thank you so much!! 😀

One more question, why when I select Account = B or C, the Penalty % in the card is different from the table? How to make the card to display the same % as the one display in the table? Sample here.

Community Champion

Hi, @PBI_newuser

I do not understand the logic of other measures. And I do not know how your desired outcome looks like.

However, I just can guess it has something to do with ALLEXCEPT function in your measure.

Please check the link below whether it is what you are looking for.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors