echo a cell to a new column

[tim-hammond]

Hello People,

I am completely new to Power Bi and i find it facinating.

My Background is infastructure HypeV and VM Ware Et Al...So excuse me if this is a simple thing

I have a web query which pulls data from an html page.

There is a column which list the team that did the job. [Red, Green, and Brown]

I have managed to make new column's which seperate them out.

Green Team = COUNTROWS(FILTER('Table 0', [CustPriority]="Green"))

and this gives me a count of the instances, but it populates each cell in the column with the same count.

Q: Using this as a halfway house I would like to simply echo a single instance of the value into a single cell in a new column, so that i can use this in the visulisation. It maybe i can do it as a single query.

This is live data from the web and i dont see how i can use the 'Edit' options

As it stands it totals them and i have to apply an average filter.

Can anyone please be so kind as to point me in the right direction?

Thank you

Tim

What 

[tim-hammond]

Hi sebastien - Brilliant - that worked - i see what you are doing now!

Thank you so much for your help 🙂

Tim

[Anonymous]

@tim-hammond,# I'm glad my solution helps you !!!

Keep going !

[itchyeyeballs]

Good to see you got it working

The potential advantage of using a measure instead would be that it would be more flexble going forward, for example if new teams were introduced you wouldn't need to add columns.

The measure could just be something as simple as Count(id_column) where id_column is any column in your data which has data for each row.

Then just put the measure into the value box and your CustPriority column into the legend box and PBI will automatically create a data set for each team.

[tim-hammond]

Thank you both for your prompt replies.

Here is what i have at present.

As you can see in order to get a visualisation of each team i have to filter it as an average - otherwise it will total them.

I have tried looking around - but i suspect my question is so simple that it is not mentioned anywhere!

I have experimented with measures but it seems that this is more related to cross reffrencing different tables.

[Anonymous · Accepted solution]

That's what I thought. Did you try to replace your formula by the one I gave you?

For example, for the first record I see, Instead of having 1246, 1577, 931, you should have 1, 0, 0

For the 4rd record, if you change the formula for the three columns, you should have 0, 0, 1.

So, the aggregation function should work.

Séebastien

[tim-hammond]

Brill - Thank you Sebastien - i did try your suggestion - but it came up with syntax errors - so i will go through it more carefully and let you know shortly!

Tim

[itchyeyeballs]

Tim,

Can you share the table structure you have at the moment and tell us which visual you are trying to use? would make it easier to visualise.

At a guess though it sounds like you may need to create a measure rather than a calculated column.

If you were creating a simple line chart you could put the column containing the team name into the legend section and the aggregating measure into the values section. PBI would then dynamically calculate the results so you don't need to "pre-calculate" as you would in Excel

[Anonymous]

Hello @tim-hammond

The formula you wrote will count all the row in you table according to the filter you put. So it will put the same data for each record.

Perhaps you could just change the formula like this :

Green Team = If([CustPriority]="Green";1;0). 

The aggregation function should be positionned on sum and I think you could do the job.

Tell me if it helps you.

Sébastien