Aggregate based on only 1 column instead of 2 columns

[Anonymous]

Hi Guys,

I have a requirement wherein I have 3 columns Group, SubGroup, Volume(Measure). Group has 2 values (A, B) and SubGroup A has 3 values (x,y,z) and subgroup B has 1 value x, and also there is a column Volume(Sum) based on each field in a table. I want to get an Output where we are able to display all three columns but the volume is aggregated based on only Group Column and does not calculate Volume(Sum) based on  Subroup Column. Is there any way we can achieve this?. Please find the example image below.

Any help would be highly appreciated.

Thanks in Advance

[mahoneypat · Accepted solution]

Instead of using your subgroup column, you could just use a measure instead.  You can then just show a simple sum of your volume column.

SubGroup Measure = var vSubGroups = VALUES(Table[SubGroup])

Return IF(COUNTROWS(vSubgroups)=1, vSubGroups, " ")  //returns a space instead of BLANK() so it shows

Regards,

Pat

[Anonymous]

Yes, Even this works, Thanks a lot for the Idea.

Thanks,

Sathwik

[lbendlin]

Have you tried not including the subgroup in your table or matrix visual? Or does your expected output really need to show the name of the single subgroup or blank if there is more than one subgroup?

[Anonymous]

Hi @lbendlin ,
I have created a custom column, in which I have renamed all the subgroups(x,y,z) = "" of Group A. So now instead of considering 3 different subgroups, the table will display it as one subgroup. This way my both goals of displaying blank in the subgroup column for that particular group (A) and aggregating it as one group only can be achieved.

Thanks