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

[PriyankaJhaTheA]

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 

[v-yanimei-msft]

Hi @PriyankaJhaTheA  , 

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.

[PriyankaJhaTheA]

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!!

[Anonymous]

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

[PriyankaJhaTheA]

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().

[Anonymous]

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