cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Aggregating Value in Concatenated Field Not Working

I have a dataset that looks effectively like this:

 

TCPeriodCOUNT
xc1P31
xc2P41
xc1P41
yc1P41
zc1P41

 

I am attempting to concatenate Col-T with an aggregation of COUNT (i.e. x1, x2, y1, z1).

 

As you can see below, whilst aggregation of the COUNT column and the appropriate measure produces the correct summed up values, when trying to aggregate T with the measure or the calculation, it refuses to work.

 

TPeriodCOUNTMeasure - SUM(Count)Column - T&SUM(Count)Column - T&Measure
xP311x5x1
xP422x5x1
yP411y5y1
zP411z5z1

 

Need to keep granularity in data table so slicers and filters work for C and Period on the front end.

 

Hoping you can assist!

9 REPLIES 9
Microsoft
Microsoft

Hi @tgardner ,

 

Based on my test, the COUNT column is created by the measure.

COUNT = COUNT('Table'[T])

 

Then create a measure to combine the two columns together.

Col-T = MAX('Table'[T])&[COUNT]

 

The result is as follows, and the slicer filter will change the result.

7.png8.png

 

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

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

Hi Stephen

 

This is a great solution to have the count dynamically change, thanks so much. Curious why I can't use this in a Treemap visualisation (so x1, y2 etc show on the map with appropriate proportions) and if you know how this can be achieved?

Hi @tgardner ,


Sorry to disturb you...


But did I answer your question ? Please mark my reply as solution. Thank you very much.

 

Best Regards,
Stephen Tao

Hi Stephen,

 

Not a bother in the slightest. I think we are very near a solution - you've really helped.

 

I'm not trying to put the text into the values field. I'm hoping to use the count of that concatenated text in the values field and the concatenated text displayed in the tree map. Does that make sense?


Thanks!!

Hi @tgardner ,

 

Because the format of the Col-T measure is text, we can't put a text format measure into Values in the Treemap.

32.png

 

 

 

Best Regards,

Stephen Tao

 

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

Super User IV
Super User IV

@tgardner ,

New measure = max(Table[T])  & divide(sum(Table[COUNT]), count(Table[COUNT]))

 

New measure 2 =max(Table[T])  & calculate(sum(Table[COUNT]), allselected(Table))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi

 

measure = MAX('Table'[T])&CALCULATE(SUM('Table'[COUNT])) works in terms of displaying this in a table.

but this measure cannot be placed in a tree map. do you know how to put this in a treemap?
Super User IV
Super User IV

@tgardner , Not able to understand your output and what is not working



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I would like to Concatenate Column  T and a sum/count of COUNT column, i.e. x2, y1

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors