topic Re: Range counting number of invoice per user in DAX Commands and Tips

Range counting number of invoice per user

Martin0011 — Thu, 17 Aug 2023 09:05:22 GMT

Dear community,

I have a table with a list of Invoice and another one with a list of User where the invoice is assigned. An invoice can be assigned to multiple users.

What I'm trying to acheive is to have a simple table visual where we can have a range and for each of the range catagory, we would have 2 values : Number of User that have the number of invoice pending to them.

Range	# User	# Of invoice
0	12	0
1-3	789	1500
3-10	45	250
>11	46	500

I also have 2 type of invoices : Awaiting approval & Pending action.
I need that the number of user and invoice allocated to a range be dynamic based on the filter.

When I create a measure to acheive this, the result is always > 11 because it does not take into account the user

When I create a new colmun, the filter will change the number of invoice but the user is always assigned to the same range.

how can we acheive this ?

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 10:20:10 GMT

Hi Martin0011 it is very hard, at least for me, to understand request / issue without data. please share sample data and expected output.

Based on your text only, if you already have some measure/s additional work should be (include table or some part of table "because it does not take into account the user")

"An invoice can be assigned to multiple users. " I undestand this part: your results could be seen in user / invoice perspective, so model is key.

Re: Range counting number of invoice per user

Martin0011 — Thu, 17 Aug 2023 10:41:50 GMT

Ok, I'll try to be more pricised.

I have tables

- Invoices (fields : Invoice & Status)

- User_Inv (fields : User & Invoice)

Measure :

# Inv = COUNTROWS(Invoices)

Group = SWITCH( true(), [# Inv]=0,"No Invoice", [# Inv] <4,"1. 1-3", [# Inv]<11,"2. 4-10", [# Inv]<51,"3. 11-50", "4. >50")

The end goal is to have a simple table visual with :
Group - # of User - # of Invoice

Range	# User	# Of invoice
0	0	0
1-3	0	0
4-10	1	10
11-50	1	14
>50	0	0

I alsways need to know the number of user that would be assigned to a group. In the above table, i know that I have 1 user that have

I works when I add the user inside the table but if I remove, the measure will count the number of invoice that will not take the user into account.

See that User B has 14 invoices and User 1 only 10, when I add the user in the visual, it provide the right group based on the invoice volume.
Now, on the right table, I remove the user and obvisouly, the measure #Invoice is the total number of incoice and then, the group is calculated.

This should also work when appliying filters - If I select Awaiting Approval, then, the total volume of invoice will decrease, the user will be allocated to another group then.

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 12:03:31 GMT

Hi Martin0011 what about relationships in model? in which table you have possbile double values for invoices / users, both or only one of tables?

The best would be to share sample data and expected results. Most probably your measure do not include "sum as expected", but more details is needed.

Re: Range counting number of invoice per user

Martin0011 — Thu, 17 Aug 2023 13:17:31 GMT

some_bih

Relationship is between the 2 table using the field Invoice.

Invoice & Status are Unique value

Invoice & User can have multiple value as 1 invoice can be assigned to N user.

I'm trying to share a PBIX file and ZIP but it does not allow me to upload this here.

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 13:34:08 GMT

Hi Martin0011 if your have office 365 create link and share it, or put link from some links like dropbox.. (do not forget underlying excel file or ...)

Re: Range counting number of invoice per user

Martin0011 — Thu, 17 Aug 2023 14:08:05 GMT

Hi some_bih my professional compture does not allow to share link outside the org.
Here is another file. https://1drv.ms/u/s!ApG1DjiT2yKRuZYrTDUmBNmaCIpO5g

there is no excel, only manual entry to test the logic.

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 14:13:04 GMT

Hi Martin0011 ok, I will see file and see what I can do.

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 14:24:53 GMT

Hi Martin0011 usually, users are assigned to invoices, meaning all invoices are allocated to users and other way around, this should be best practice and common sense.

In your case, according to picture it is not best practice? Should it be that way as shown on picture below?

Re: Range counting number of invoice per user

Martin0011 — Thu, 17 Aug 2023 14:34:42 GMT

HI some_bih

1 invoice can be assigned to multiple users - isn't it what is expressed in the relationship ?

Re: Range counting number of invoice per user

some_bih — Thu, 17 Aug 2023 15:49:23 GMT

Hi Martin0011 I just noticed it. Based on file what is expected result per user, status... Your file do not have data as described in your first post.

Re: Range counting number of invoice per user

Martin0011 — Tue, 22 Aug 2023 10:54:13 GMT

Anyone has any idea how to achieve this ?