Sum of a measure after grouping by another measure

[anushkavp27]

Hello, 

I have the data in the following format: 

Greater? is a measure having value either 1 or 0. And I have another measure called GroupByMeasure with which I want to group by. 

So, GreaterTotals should give me the summation of the Greater? measure after grouping by measure GroupByMeasure.

In short, all 3 rows here should have GreaterTotals as 1 which is a summation of 1+0+0 because it has the same group by measure called "MFJWN 2025Q01".

I have the following formula for GreaterTotals but I believe it is just replicating Greater? measure

GreaterTotals =
sumx(
    VALUES(Table[GroupByMeasure]), [Greater?]
)

Any help here would be appreciated.

[Anonymous]

Hi @anushkavp27 

You can refer to the following measure.

e.g 

GreaterTotals =
VAR a = [GroupByMeasure]
RETURN
    SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [GroupByMeasure] = a ), [Greater?] )

Best Regards!

Yolo Zhu

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

[anushkavp27]

Thank you for your response. Firstly, I had to add VALUES in the first line for it to be acceptable and not red underlined. 
But after adding that, it is not giving the right value. It is giving a count of the number of rows in there i believe.

More context: Greater? is a measure containing value either 0 or 1. It is calculated by finding out if one attribute is greater than the other in a table. If greater, then return 1. 

Then I added a GroupByMeasure which is essentially a concatenation of 2 columns in the table separated by a simple space. 

Now, I am trying to add all the values of Greater? after grouping by the GroupByMeasure. So all these 3 rows should have the same GreaterTotals value = 1 (1+0+0).

[Anonymous]

Hi @anushkavp27 

Based on your description, the  GroupByMeasure is a calculated column, you need to change the code to the following.

Measure =
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        [GroupByMeasure] IN VALUES ( 'Table'[GroupByMeasure] )
    ),
    [Greater?]
)

Best Regards!

Yolo Zhu

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

[anushkavp27]

Hey
I am afraid that gave the same result as the previous command. I am also providing the other measure formula in case it would help. 

GroupByMeasure = CONCATENATE(RELATED('Part Table'[part_nbr]), CONCATENATE(" ", RELATED(fiscal_calendar_vw[fiscal_quarter])))

Greater? =

var num = sum('Table1'[demand_qty])

var den = sum('Table 3'[supply_qty])

RETURN

if(num>=den,1,0)

Lastly, I am also providing the actual use case that I am trying to solve. Any other suggestion apart from what I have been trying to solve would also help.
Essentially, this is what my data visual looks like:

This is a matrix with part number in every row and the column is divided by each month number in the quarter (in this case it is month - 1,2,3 of Quarter 1 of the year 2025). The value inside of the matrix is that of Greater? measure which is created.
The ask was to have filter that would only showcase those rows where Greater? measure is the value one (1) in either of the 3 columns. In other words, if the value is zero in either of the 3 columns, then we should not show that row completely.
 
So my approach was to sum the Greater? measure by grouping the GroupByMeasure shown above that is a concatenation of the part_nbr and the Quarter that these 3 months belong to. Eventually, if this GreaterTotals value was greater than or equal to 1, then I would show that part_nbr in the table, else I would exclude it from the visual completely.

Now, I am open to alternative suggestions. I am afraid I cannot show more of the underlying data due to company policies. 

[Anonymous · Accepted solution]

Hi @anushkavp27 

Try the follwing measure.

measure =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( fiscal_calendar_vw ),
        [fiscal_month],
        [fiscal_quarter]
    )
VAR b =
    ADDCOLUMNS (
        CROSSJOIN ( ALLSELECTED ( 'Part Table'[part_nbr] ), a ),
        "Groupby",
            [part_nbr] & " " & [fiscal_quarter],
        "Greats", [Greater?]
    )
VAR c =
    SELECTEDVALUE ( 'Part Table'[part_nbr] ) & " "
        & SELECTEDVALUE ( fiscal_calendar_vw[fiscal_quarter] )
RETURN
    SUMX (
        FILTER ( b, [part_nbr] IN VALUES ( 'Part Table'[part_nbr] ) && [Groupby] = c ),
        [Greats]
    )

Best Regards!

Yolo Zhu

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

[anushkavp27]

Hi @Anonymous 
This worked great! Thank you so much.
One last request, can you give a small gist or a description of this measure formula that you provided? 

[Daniel29195]

hello @anushkavp27 

please provide sample data  .

best regards