Forum Discussion
Groupby function with round calculation error
Hi there.
During the last week, I've been learning about DAX working with the WideWorldImportersDW database, and I've got this question, using summarizecolummns I can get a rounded number when calculating my expression.
EVALUATE
SUMMARIZECOLUMNS (
'Dimension City'[State Province],
'Dimension City'[City],
'Dimension Date'[Fiscal Year],
FILTER('Fact Sale', RELATED('Dimension City'[State Province])="Washington" && RELATED('Dimension City'[City])="Midland"),
"Total Sales SC", ROUND(SUMX('Fact Sale', 'Fact Sale'[Quantity]*'Fact Sale'[Unit Price]),0)
)
However, if I try to do the same with the Groupby function, I'm getting this error:
EVALUATE
GROUPBY (
FILTER('Fact Sale', AND(RELATED('Dimension City'[State Province])= "Washington",RELATED('Dimension City'[City])="Midland")),
'Dimension City'[State Province],
'Dimension City'[City],
'Dimension Date'[Fiscal Year],
"Total Sales GB", ROUND(SUMX(CURRENTGROUP(), 'Fact Sale'[Quantity]*'Fact Sale'[Unit Price]),0)
)
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().
Is there a way to solve this in the same piece of code? Or is it necessary to use calculate?
1 Reply
Please refer from https://docs.microsoft.com/en-us/dax/groupby-function-dax
Note: The expression used in GroupBy may include any of the “X” aggregation functions, such as SUMX, AVERAGEX, MINX, MAXX, etc. and when one of these function is used in this way, we allow the table argument (which normally must be a table expression) to be replaced by a special CURRENTGROUP() function as described elsewhere in this document.
Restrictions on expression:
- The CALCULATE function (and therefore measures) are not allowed in the expression.
- The CURRENTGROUP function may only be used at the top level of table scans in the expression. That is, SUMX(<table>,SUMX(CURRENTGROUP(…), …)) is not allowed. ABS( SUMX(CURRENTGROUP(), [Column] ) ) is allowed, since ABS does not perform a scan.Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
