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.
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 |
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] )
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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 accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |