Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Distributor billing in a month

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 1Product 2Product 3
Once a week10814
Twice a week211210
Thrice a wek51612

 

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.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    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

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    8 years ago

    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