topic Re: URGENT!! Get Sum of rows by grouping based on column in DAX Commands and Tips

URGENT!! Get Sum of rows by grouping based on column

Anonymous — Thu, 07 Mar 2024 14:17:36 GMT

Hello, Power Bi community!

I need some help with the below code:

Can you modify this dax code such that every df_Brands[Category Purchased] value, gives me a different Total Sum, So in simple words it groups by the count rows of the df_Brands table based on the df_Brands[Category Purchased] column.

TotalSum_CategoryOccasions_MultiSelect_Part2 demo = CALCULATE( SUMX( VALUES(Respondent[Unique_ID]), VAR OccasionsYes = CALCULATE(COUNTROWS('df_Occasions'), 'df_Occasions'[value1] = "Yes") VAR CategoryYes = CALCULATE(COUNTROWS('df_Category Purchased'), 'df_Category Purchased'[value] = "Yes") RETURN IF( OccasionsYes > 0 && CategoryYes > 0 && ( COUNTROWS(FILTER(Respondent, Respondent[IsBrands] = TRUE())) > 0 ), 1, 0 ) ) )

@MFelix , @amitchandak , @Ashish_Mathur

Re: URGENT!! Get Sum of rows by grouping based on column

Anonymous — Thu, 07 Mar 2024 14:30:50 GMT

you could try use GROUPBY, like this :
TotalSum_CategoryOccasions_MultiSelect_Part2_Modified =
SUMX(
GROUPBY(
df_Brands,
df_Brands[Category Purchased],
"TotalSum",
CALCULATE(
SUMX(
VALUES(Respondent[Unique_ID]),
VAR OccasionsYes = CALCULATE(COUNTROWS('df_Occasions'), 'df_Occasions'[value1] = "Yes")
VAR CategoryYes = CALCULATE(COUNTROWS('df_Category Purchased'), 'df_Category Purchased'[value] = "Yes")
RETURN
IF(
OccasionsYes > 0 &&
CategoryYes > 0 &&
(
COUNTROWS(FILTER(Respondent, Respondent[IsBrands] = TRUE())) > 0
),
1,
0
)
)
)
),
[TotalSum]
)

Re: URGENT!! Get Sum of rows by grouping based on column

Anonymous — Thu, 07 Mar 2024 15:00:47 GMT

Hello Anonymous
thank you for taking your time to answer this question but
i'm getting this error when i ran this code: 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().

Re: URGENT!! Get Sum of rows by grouping based on column

Anonymous — Thu, 07 Mar 2024 16:31:08 GMT

Maybe u can use a function summarizecolumns to solve this.
like this
TotalSum_CategoryOccasions_MultiSelect_Part2_Modified =
SUMX(
SUMMARIZECOLUMNS(
df_Brands[Category Purchased],
"TotalSum",
CALCULATE(
SUMX(
VALUES(Respondent[Unique_ID]),
VAR OccasionsYes = CALCULATE(COUNTROWS('df_Occasions'), 'df_Occasions'[value1] = "Yes")
VAR CategoryYes = CALCULATE(COUNTROWS('df_Category Purchased'), 'df_Category Purchased'[value] = "Yes")
RETURN
IF(
OccasionsYes > 0 &&
CategoryYes > 0 &&
(
COUNTROWS(FILTER(Respondent, Respondent[IsBrands] = TRUE())) > 0
),
1,
0
)
)
)
),
[TotalSum]
)

Re: URGENT!! Get Sum of rows by grouping based on column

v-yanimei-msft — Fri, 08 Mar 2024 02:10:08 GMT

Hi Anonymous ,

Thank you for giving us a specific DAX code and a detailed description of the problem. Based on your description, it seems like you want to calculate the sum of each category in the column df_Brands [Category Purchased], considering specific criteria. Here's a revised version of the DAX formula that should meet the requirements:

SUMX( VALUES(df_Brands[Category Purchased]), VAR CurrentCategory = df_Brands[Category Purchased] VAR OccasionsYes = CALCULATE(COUNTROWS('df_Occasions'), 'df_Occasions'[value1] = "Yes", 'df_Brands'[Category Purchased] = CurrentCategory) VAR CategoryYes = CALCULATE(COUNTROWS('df_Category Purchased'), 'df_Category Purchased'[value] = "Yes", 'df_Brands'[Category Purchased] = CurrentCategory) RETURN IF( OccasionsYes > 0 && CategoryYes > 0 && ( COUNTROWS(FILTER(Respondent, Respondent[IsBrands] = TRUE() && Respondent[Category Purchased] = CurrentCategory)) > 0 ), 1, 0 ) )

If you have any challenges or other issues implementing this solution, can you share the specific data ( including table and model ) you have used in text and image format? This will help diagnose the problem more effectively.

Best Regards,
Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: URGENT!! Get Sum of rows by grouping based on column

Anonymous — Fri, 08 Mar 2024 09:20:21 GMT

Hello v-yanimei-msft

Thank you for taking the time to answer this question.
I implemented this code but got only 1 for the brands in my table:

Here is the link to the sample pbix for you: https://drive.google.com/file/d/1JQbD4oyYVi5cLjne7oxOZyZ_MKDUh-dW/view?usp=sharing

Please refer to this and see if your able to help me out!!