Count the order number by Account

[PBI_newuser]

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

[Jihwan_Kim · Accepted solution]

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] )

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

[PBI_newuser]

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]

[Jihwan_Kim]

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] )

[PBI_newuser]

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.

[Jihwan_Kim]

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