DISTINCCOUNT where you have two DATA tables is it possible.

ljtrzebinski

New Member
Joined
Jul 7, 2016
Messages
6
Hello all,

I hope that there is a solution to a specific issue I run into. I have two different sales table one is for all "organic sales" and the other one is for sales that resulted as part of "order sharing" process between other companies". Both data tables have a relationship to a job table that has specific job number associated to each sales I am trying to calculate total number of providers on assignment regardless if the sale was organic or part of the order sharing. I am able to calculate number of providers for order sharing and number of providers for organic sales. But is it possible to get distinct number of providers on assignment regardless where the order initiate? Currently I have couple providers that are listed in both.

Number of providers Organic:=calculate(distincount(Jobs[Provider_Name]),OrganicSales)
Number of Providers OrderSharing:=calculate(distincount(jobs[Provider_name]),OrderSharing)
Number of providers All:=calculate(distincount(jobs[Provider_Name]),OrganicSales&&OrderSharing) - Error The Expression refers to multiply columns. Multiply columns cant be converted to scalar value.

Is there another way to do this?

Thanks,
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Your syntax looks a bit weird to me (in general) -- as it sorta looks like a pattern for solving many to many problems. That said... if a simple distincount(jobs[Provider_Name]) isn't what you want, maybe try: calculate(distincount(jobs[Provider_Name]), OrganicSales , OrderSharing)
 
Upvote 0
Your syntax looks a bit weird to me (in general) -- as it sorta looks like a pattern for solving many to many problems. That said... if a simple distincount(jobs[Provider_Name]) isn't what you want, maybe try: calculate(distincount(jobs[Provider_Name]), OrganicSales , OrderSharing)


I did try your last suggestion but it will give me (blank).

Thanks though.
 
Upvote 0
Hi there,

1. First suggestion is to combine OrganicSales and OrderSharing into a single Sales table, with say a SalesType column that is either "OrganicSales" or "OrderSharing".
This is only possible if OrganicSales and OrderSharing have consistent columns.
Then the DAX is simpler for the combined Distinct Count, and you would end up with:
Code:
[B]Number of providers Organic[/B] :=
CALCULATE ( DISTINCTCOUNT ( Jobs[Provider_Name] ), Sales, Sales[SalesType]="OrganicSales" )

[B]Number of providers OrderSharing[/B] :=
CALCULATE ( DISTINCTCOUNT ( Jobs[Provider_Name] ), Sales, Sales[SalesType]="OrderSharing" )

[B]Number of providers All [/B]:=
CALCULATE ( DISTINCTCOUNT ( Jobs[Provider_Name] ), Sales )

2. Second suggestion, if you don't want to combine tables and are using the latest version of DAX (with the UNION function):
Code:
[B]Number of providers All [/B]:=
CALCULATE (
    DISTINCTCOUNT ( Jobs[Provider_Name] ),
    UNION (
        SUMMARIZE( OrganicSales, Jobs[job_number] ),
        SUMMARIZE( OrderSharing, Jobs[job_number] ),
    )
 )
3. Third suggestion, if you don't want to combine tables and are not using the latest version of DAX:
Code:
[B]Number of providers All [/B]:=
CALCULATE (
    DISTINCTCOUNT ( Jobs[Provider_Name] ),
    FILTER (
        VALUES ( Jobs[job_number] ),
        CALCULATE ( COUNTROWS ( OrganicSales ) + COUNTROWS ( OrderSharing ) )
            > 0
    )
)
 
Upvote 0
[/CODE]
3. Third suggestion, if you don't want to combine tables and are not using the latest version of DAX:
Code:
[B]Number of providers All [/B]:=
CALCULATE (
    DISTINCTCOUNT ( Jobs[Provider_Name] ),
    FILTER (
        VALUES ( Jobs[job_number] ),
        CALCULATE ( COUNTROWS ( OrganicSales ) + COUNTROWS ( OrderSharing ) )
            > 0
    )
)
[/QUOTE]

Thank you the last one worked perfectly. Thanks so much.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top