Count rows depending of times that appears, by month

[jlarques]

Hi Community,

I have a table with many rows where the company sent packages in different dates, and I want to count how many clients has been sent packages in one month, so even if the company has sent many packages to the same client, count only once to be able to know how many clients the company sent in one month.

I attach an image with the original table and other table with the result that the company wants to get.

How can I do it?

Thanks everyone for all your help and support.

[v-shex-msft]

HI @jlarques,

You can create a table visual with 'customer name' and date fields(switch to hierarchy model with year, month level) with the below measure formula to get the count of rows.

formula =
VAR rowCount =
    CALCULATE (
        COUNTROWS ( Table ),
        ALLSELECTED ( Table ),
        VALUES ( Table[Customer Name] ),
        VALUE ( Table[Date] )
    )
RETURN
    IF ( rowCount >= 1, 1, 0 )

Regards,

Xiaoxin Sheng

[jlarques]

Hi @v-shex-msft,

it works, but the matrix total is one. How can I count all ones to know the real total? 

Thanks again for your help, @v-shex-msft 

[v-shex-msft · Accepted solution]

Hi @jlarques,

What type of total do you want? Aggregate the current calculated values or show the total amount of these records? If you mean to summary the current results, you can try to use the following measure formula to replace the old one:

formula =
VAR summary =
    SUMMARIZE (
        Table,
        Table[Customer Name],
        Table[Date],
        "Tag", IF ( COUNTROWS ( Table ) >= 1, 1, 0 )
    )
RETURN
    SUMX ( summary, [Tag] )

BTW, this should be a common issue when Dax expressions calculate on the total level (multiple aggregates on Dax expressions), you can take a look at the following blog to know more about these:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[jlarques]

Hi @v-shex-msft,

I accepted your proposal as a solution, because I wanted to show the total amount. So, your example is good for me. Appreciate your link to Measure Total, The Final Word.

As always, thanks again the community for all the help and support, specially @v-shex-msft  and @Samarth_18.

Regards,

[jlarques]

Hi @v-shex-msft,

thanks for your answer. I cannot test it until next Sunday, because I'm on Easter holidays without my laptop.

I'm going to try it when I arrive to my home and I'll tell you if it's work or not.

Thanks for your help.

[jlarques]

Hi 

[jlarques]

Hi @Samarth_18 ,

An Html error message appears when I try to paste the table:

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

I tried many times but it's not possible.

[Samarth_18]

Hi @jlarques ,

Could you please copy+paste the source table instead of screenshot.

Thanks,

Samarth

[jlarques]

Hi @Samarth_18 ,

I cannot attach any file here but I can send you the Excel file to you.

Thanks,

[Samarth_18]

@jlarques Just paste your source table here. You dont need to attach file.