Get a total of values from a column and group the results by their value

[PhilB]

Hi, I'm sorry if my title was a little vague im not sure how i would have worded it really.

Hopefully someone can help, im relatively new to Power BI.

Currently i have a database that i use to store multiple events and manage them (its a WordPress database with an events plugin).

In the transactions table i have a column of 'STS_ID' which can have one of four values....

TCM = transaction complete

TIN = transaction incomplete

TAB = Transaction abandoned

TFL = Transaction failed

What i would like to do is make a count of the 4 values in the column and get the number of transactions with each status code. I can count the entire column but that just gives me an over all total. Ideally i would like to be able to get these numbers and just place them in a number card on the dashboard. I know that i can make a slicer tile with the transaction codes in and using this would filter the results shown depending on the transaction. I would like to work out the amount for each code though and then just give the numbers to the end user without them having to interact too much with slicers and the visualisations.

I tried with 

Transactions Complete = COUNTA(table_name[STS_ID])

And also tried making one for each code but all they are doing is giving me the total of the values in the column. 

Really what i guess im really asking is this. Is there a function that will count the values in that column, group them into the 4 groups, one for each status code and then provide me with a usable result for each one? so i can show how many of each type of transaction code there is in the database table/column.

Sorry if i havent been very clear here, but if someone could point me in the right direction or thinks they may be able to help with a little bit more information then il try and provide as much as i can.

Thanks

PhilB

[PhilB]

Hello again guys,

So it turns out i jumped the gun slightly when i replied saying it worked.

The measures do work but now i have another issue. I had used the measures as directed but now it seems when i click on my pie chart it filters the results in the number cards. Is there a way to keep these numbers static? I'd just like to show an overall count of transactions by code. 

Should i ask this in a seperate topic? 

Thanks again

PhilB

[Habib]

You can use Edit Interactions option to disable filtering on your results. You need to select the Pie Chart and then click on Edit Interactions.

[PhilB]

Awesome! thank you @Habib thats got it.

Many thanks to everyone for their input.

PhilB

[v-qiuyu-msft · Accepted solution]

Hi @PhilB,

Based on my understanding, you want to return four values for each "STS_ID" group instance, then place those count values in separate card visual, right?

In your scenario, you can create four measures with Calculate() and Filter() functions like below:

Transactions Complete = CALCULATE(COUNTROWS(transactions),FILTER('transactions','transactions'[STS_ID]="TCM"))

Transactions Incomplete = CALCULATE(COUNTROWS(transactions),FILTER('transactions','transactions'[STS_ID]="TIN"))

Transactions Abandoned = CALCULATE(COUNTROWS(transactions),FILTER('transactions','transactions'[STS_ID]="TAB"))

Transactions Failed = CALCULATE(COUNTROWS(transactions),FILTER('transactions','transactions'[STS_ID]="TFL"))

Then drag those measures to four card visuals separately.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[PhilB]

Thank you @v-qiuyu-msft you seem to have hit the nail on the head there. I've just seen this so i will give it a go this morning and report back.

Thank you, much appreciated!

Edit: Sorry @Greg_Deckler i hadnt seen your reply until after i replied. Thank you for your suggestion. Il be trying them this morning.

PhilB

[PhilB]

Hi guys, it would seem that your method cracked it straight away @v-qiuyu-msft. It works as expected now, i had a slight difference in numbers when i checked but i had forgotten to refresh my data.

@Greg_Deckler would i be correct in thinking the difference between your method is that @v-qiuyu-msft method filters the results in the measure where as yours i would filter in the visualisation level?

Both seem good, i have accepted @v-qiuyu-msft post as the solution as it seems to eliminate the need to filter the visualisation itself which is something i could quite easiy forget to consider in future. 

Many thanks guys, i appreciate both of you helping. Is there a way to close the topic as resolved? or shall i just leave it open?

Regards,

PhilB

[Greg_Deckler]

I *think* what you want is to do something like a measure with the formula:

Transactions Complete = COUNTROWS(table_name)

Then, create 4 card visuals and put this measure in them. Then, use the visual filter controls to filter each card to the right "type" of STS_ID.