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.
Hello,
Please see below,
I want to group “KEY” column once I group same key ,In a new column I want to display total quantity and total sales price.
I have used Group by and I am getting below error,
test = GROUPBY('2018 RAW Data','2018 RAW Data'[Key],'2018 RAW Data'[Comment],"Invoice Total",SUMX('2018 RAW Data','2018 PY RAW Data'[Quantity]))
“Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().”
Can any one suggest me some way?
Source | ||
Key | Quantity | Sales Price |
ACB-123-State | 1 | 10 |
ACB-123-State | 2 | 20 |
ACB-456-Provience | 3 | 15 |
ACB-456-Provience | 4 | 5 |
Expected Result | ||
Key | Quantity Total | Sales Price Total |
ACB-123-State | 3 | 30 |
ACB-456-Provience | 7 | 20 |
Solved! Go to Solution.
give this a try:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS(Table1[Key]), "Total Qty", CALCULATE( SUM( Table1[Quantity])), "Total Sales Price", CALCULATE( SUM( Table1[Sales Price])) )
give this a try:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS(Table1[Key]), "Total Qty", CALCULATE( SUM( Table1[Quantity])), "Total Sales Price", CALCULATE( SUM( Table1[Sales Price])) )
Thank you so much Nick.
@Anonymous no problem. And just so you know, you don't have to physically create this table if you dont want to. You can use anywhere where you a table is allowed, in say, SUMX.
Perfect & Thank you so much Nick.
Hi Nick,
Thank you so much for your suggestion.
When i tried your suggestions i am getting below error,
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.".
Thanks
@Anonymous wrote:give this a try:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS(Table1[Key]), "Total Qty", CALCULATE( SUM( Table1[Quantity])), "Total Sales Price", CALCULATE( SUM( Table1[Sales Price])) )
Are you creating this as a new table, or as a new measure? This should be a new table
Hi,
i do not want new table i want two new columns.
Thanks
@Cmcmahan wrote:Are you creating this as a new table, or as a new measure? This should be a new table
Hi
@Cmcmahan suggestion is correct. You need to have a new table, not additional columns. The expected result you showed us is a summarized table, with a different grain (one row per each key).
Thanks for your suggestion.
You both are right and it is giving me the right numbers.
Thanks Again.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |