cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
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
A5
B3
C2
Total10

 

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



1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @PBI_newuser 

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

 

Picture4.png

 

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

 

https://www.dropbox.com/s/f1203rsgdmonu5y/newuser.pbix?dl=0 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

5 REPLIES 5
Jihwan_Kim
Community Champion
Community Champion

Hi, @PBI_newuser 

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

 

Picture4.png

 

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

 

https://www.dropbox.com/s/f1203rsgdmonu5y/newuser.pbix?dl=0 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

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

I changed the condition by replacing the "5" with a measure. Could you please help? Thanks.

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]

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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.

PBI_newuser_0-1623661332906.png

 

PBI_newuser_1-1623661354781.png

 

 

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.

 

https://www.dropbox.com/s/2wf5fvbzhybccqw/newuser_Max%20Order%20%281%29.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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