Distributor billing in a month

[Anonymous]

Hello all,

I have a sales table for primary sales. The sales data comes at Day, City, Product & Customer level.

Now I have to show the following for a month selection: (Month field is in a date table linked to the sales table)

	Product 1	Product 2	Product 3
Once a week	10	8	14
Twice a week	21	12	10
Thrice a wek	5	16	12

I have to show the frequency of the times that a product is billed for customers.

For eg: Product 1 is billed once a week for 10 customers, twice a week for 21 customers and thrice a week for 5 customers.

Please help me derive this logic.

Need urgent help.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

First, you may go to Query Editor to use Week of Year to add a column.

Second, use IF or SWITCH Function to new a column to calculate different times a week. For example:

Times =
IF (
    COUNTX (
        FILTER (
            Table1,
            Table1[Week of Year] = EARLIER ( Table1[Week of Year] )
                && Table1[Product] = EARLIER ( Table1[Product] )
        ),
        Table1[Product]
    )
        = 1,
    "Once a week",
    "Twice a week"
)

Third, add a measure to calculate the numbers of customers.

NumberOfCustomers =
CALCULATE ( DISTINCTCOUNT ( Table1[Customer] ) )

Regards,

Cherie