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 da...
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

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft 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