bar chart calculation

[akfir]

Hi dear helpers,
i have this kind of data table and i wish to build the correct measures/calculations in order to show this kind of bar chart (attached below).
i actually need to calculate how many customers were sent X messages successfully.
can you please assist me with the right DAX for it?

for the below data:
in Jan'22, 1 customer (id=2) has been delivered 1 message successfully, and 2 customers (id=1,3) has been delivered successfully 2 different massages

thanks in advance.

[Anonymous]

Hi akfir

I succeed but I think there is an easy way to do it.

the table name with datas on this page have this name here : 'BAR CHART CALL (2)'

First I create a new table :

Nbre Message = var _step1=

ADDCOLUMNS(

SUMMARIZE('BAR CHART CALL (2)','BAR CHART CALL (2)'[Customer ID]),

"@NbMess",calculate(COUNT('BAR CHART CALL (2)'[Message ID]),'BAR CHART CALL (2)'[Delivered]="OK",KEEPFILTERS('BAR CHART CALL (2)')))

return

_step1

After a second table with generate

Serie message = GENERATESERIES(1,10,1)

and finally add a column to this table ==>

Cust = var nbmess='Serie message'[Value]

var result=calculate(count('Nbre Message'[Customer ID]),'Nbre Message'[@NbMess]=nbmess)

return

result

At the end I used the last table ot make the graph.

Hope this helps but I am pretty sure that there is an easy way to do it !! 🙂

[Arul · Accepted solution]

@akfir ,

first you need to create month name column in your table like below using below code

Month = FORMAT('Messages Delivered'[Date],"MMM-YY")

then create a measure using below logic then use it in your visual you will get your desired result,

Count of meassage = 
CALCULATE(COUNT('Messages Delivered'[Customer ID]),'Messages Delivered'[Delivered?]="TRUE")

Thanks,

Arul