cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

Need urgent help.

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.