cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Aggregate based on only 1 column instead of 2 columns

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.

1 ACCEPTED SOLUTION
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Anonymous
Not applicable

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

Thanks,

Sathwik

Super User

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
Not applicable

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors