Count the Over all Data, Success and Failure in Column Chart

[Krishna_Newuser]

Hello,
Good day!!!

Need help to give the output in the below format using the below tables using DAX and in Column Chart. Tried to take the data but its not showing as I could see discrepancies.  I will put the TxnDt, Day in  X Axis and other columns from the WT table into Y Axis. Using the Select Store Table to filter all the stores from filter All Pages.

Used the below filter function but showing the count in Success and Abandoned as well.

FYI:  Same Conversation ID we have two row one is “Success” and another one is “Failure”.

Below DAX used but not working.
Check out = CALCULATE(COUNTROWS('WalkieTalkieUsage (2)'), 'WalkieTalkieUsage (2)'[ ClientCallStatus] = "Failure")

Data Check = CALCULATE(COUNT('WalkieTalkieUsage (2)'[ ClientCallStatus]), 'WalkieTalkieUsage (2)'[ ClientCallStatus] = "Failure")

Expected Output:

Date Table:

TxnDt	Day	Date4
17-Nov-24	Sun	[7] - Sun 17.011.2024
18-Nov-24	Mon	[1] - Mon 18.011.2024
19-Nov-24	Tue	[2] - Tue 19.011.2024
20-Nov-24	Wed	[3] - Wed 20.011.2024
21-Nov-24	Thu	[4] - Thu 21.011.2024
22-Nov-24	Fri	[5] - Fri 22.011.2024
23-Nov-24	Sat	[6] - Sat 23.011.2024

Select Store:

StoreName	StoreNo
London Colney	4734
Salisbury	1931
Cheshunt	97
Leeds White Rose	1668
Plymouth	2671

WT Usage Table:

ConversationId	TxnDt	StoreName	StoreNo	ClientCallStatus
ABCD	Sunday, November 17, 2024	London Colney	4734	SUCCESS
ABCD	Sunday, November 17, 2024	London Colney	4734	FAILURE
JJKL	Sunday, November 17, 2024	London Colney	4734	SUCCESS
NJHJ	Sunday, November 17, 2024	Stevenage Retail Park	1603	ABANDONED
CCHQ	Sunday, November 17, 2024	Stevenage Retail Park	1603	ABANDONED
FURTH	Sunday, November 17, 2024	Stevenage Retail Park	1603	ABANDONED
FURTH	Sunday, November 17, 2024	Stevenage Retail Park	1603	SUCCESS
HJUK	Sunday, November 17, 2024	Stevenage Retail Park	1603	ABANDONED
QTH	Sunday, November 17, 2024	Stevenage Retail Park	1603	ABANDONED
KJL	Sunday, November 17, 2024	Stevenage Retail Park	1603	FAILURE
PQER	Sunday, November 17, 2024	Stevenage Retail Park	1603	FAILURE
YHJ	Sunday, November 17, 2024	Stevenage Retail Park	1603	FAILURE
YHJ	Sunday, November 17, 2024	Stevenage Retail Park	1603	SUCCESS
ABCD	Sunday, November 17, 2024	Stevenage Retail Park	1603	FAILURE
ABCD	Monday, November 18, 2024	Salisbury	1931	ABANDONED
NNMN	Monday, November 18, 2024	Salisbury	1931	FAILURE
JKLM	Monday, November 18, 2024	Salisbury	1931	FAILURE
TYPL	Monday, November 18, 2024	Salisbury	1931	FAILURE
GGDF	Tuesday, November 19, 2024	Cheshunt	97	ABANDONED
HH	Tuesday, November 19, 2024	Cheshunt	97	ABANDONED
NNMN	Tuesday, November 19, 2024	Shrewsbury	262	FAILURE
NNMN	Tuesday, November 19, 2024	Shrewsbury	262	SUCCESS
HAR	Tuesday, November 19, 2024	Leeds White Rose	1668	SUCCESS
FQRS	Tuesday, November 19, 2024	Leeds White Rose	1668	FAILURE
FQRS	Tuesday, November 19, 2024	Leeds White Rose	1668	ABANDONED
QTP	Wednesday, November 20, 2024	Plymouth	2671	SUCCESS
FP	Wednesday, November 20, 2024	Plymouth	2671	FAILURE
KKM	Wednesday, November 20, 2024	Plymouth	2671	ABANDONED
SSMS	Wednesday, November 20, 2024	Plymouth	2671	ABANDONED
HHM	Thursday, November 21, 2024	Plymouth	2671
FFMP	Thursday, November 21, 2024	Plymouth	2671	SUCCESS
TTNT	Thursday, November 21, 2024	Plymouth	2671	FAILURE
HHMT	Thursday, November 21, 2024	Plymouth	2671	ABANDONED
YUP	Friday, November 22, 2024	Tamworth	2794	FAILURE
YUP	Friday, November 22, 2024	Tamworth	2794	ABANDONED
YUP	Saturday, November 23, 2024	Stevenage Retail Park	1603	SUCCESS
NGK	Saturday, November 23, 2024	Stevenage Retail Park	1603	ABANDONED

[Anonymous · Accepted solution]

Hi,@Krishna_Newuser .I am glad to help you.

Like this?
I calculate the number of Failure,Success,Abandoned calls per day for all data and I also calculate the total number of calls per day.

On the right is the percentage of each type of calls to the total number of calls recorded per day.

DIVIDE([DailyCallsKinds],[DailyCallsAll],0) 

I noticed that there are call records with a status of BLANK in the test data you gave. If this is present in your data, then calculating the total number of calls per day is necessary. (Check if there are records with status =blank)

This is the measures I create:

M_totalAll = COUNTROWS('WT Usage Table')


M_TotalFailure = CALCULATE(COUNTROWS('WT Usage Table'),FILTER('WT Usage Table','WT Usage Table'[ClientCallStatus] = "FAILURE"))


M_TotalSuccess = CALCULATE(COUNTROWS('WT Usage Table'),FILTER('WT Usage Table','WT Usage Table'[ClientCallStatus] = "SUCCESS"))


M_TotalAbandoned = CALCULATE(COUNTROWS('WT Usage Table'),FILTER('WT Usage Table','WT Usage Table'[ClientCallStatus]= "ABANDONED"))

Measures to calculate the percentage:

M_TotalFailurePercent = DIVIDE([M_TotalFailure],[M_totalAll],0) 


M_TotalSuccessPercent = DIVIDE([M_TotalSuccess] ,[M_totalAll],0)


M_TotalAbandonedPercent = DIVIDE([M_TotalAbandoned],[M_totalAll],0) 

If you want to show the specific values of the data in visual, I recommend you to turn on the following options to make it easier for you to analyse the data.

I hope my suggestions can bring you help. If there is any error in my understanding, please correct me promptly and provide more detailed data and expected results (including the establishment of relationships between tables in the model, judgement logic of calculations, etc.).
If possible, please provide a test file of pbix that does not contain sensitive data and share it on the forum via github link. This will help to solve your problem.

The Data Model relationship.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Krishna_Newuser]

This helps, Thank you so much