Distinct CountX Help

[maashi]

Hi, have tried similar Distinct countx type posts in the forum but can't seem to see an answer to what I want.

I have the following code, it is correct but I need the countx bit to be distinct and I have tried adapting workarounds found in this forum but it has not helped.

DIVIDE(

        COUNTX(

            FILTER(

                fact_NCP_Reporting,

                fact_NCP_Reporting[NCP]="NCP"

                ),

            fact_NCP_Reporting[Sales Order No]

            ),

        (

         SUMX(

            FILTER(

                fact_NCP_Reporting,

                fact_NCP_Reporting[NCP]="Sales Order"

                ),

            fact_NCP_Reporting[SQM]

            )

            /1000

        )

    )

The full dataset is almost 1000000 lines and will keep growing daily so I can't have a solution that's too cumbersome on the processing speed either.

The essential columns are below, without copying everything else in:

Sales Order No	NCP	SQM
1947525	NCP	2.5
1947529	NCP	3.5
1947529	NCP	3.3
1871488	Sales Order	0.5
1934149	Sales Order	0.9
1934149	Sales Order	1.3
1913819	Sales Order	2.1
1913819	Sales Order	1.8
1913819	Sales Order	1.9
1913819	Sales Order	0.7
1923551	NCP	1.2
1899619	Sales Order	2.7

I need to divide the number of unique sales orders (By distinct counting the sales order number column), that are classified as NCP, against the total sqm of sales orders (so summing everything in the SQM column that has 'Sales order' not 'NCP' in the same line. The sumx part of the code is fine but how do I get a distinct countx to work? There is also a date column that I haven't put into this snapshot - so I'm after a monthly value of NCP count / Square meter sales.

Thank you for your suggestions

Regards

Marcy

[maashi]

Hi Leroy

Thank you so much, that worked perfectly. I didn't think I could use Distinct Count directly, but couldnt work out that i could nest it within Calculate.

Have a great day

Marcy

[Anonymous · Accepted solution]

HI,@maashi 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. I updated your CountX section to the following measure:

Measure 2 = CALCULATE(DISTINCTCOUNT(fact_NCP_Reporting[Sales Order No]),FILTER('fact_NCP_Reporting','fact_NCP_Reporting'[NCP]="NCP"))

3.The output is as follows:

4.All code updates are as follows:

Measure 3 = DIVIDE(

         CALCULATE(
            DISTINCTCOUNT(
                fact_NCP_Reporting[Sales Order No]),
                FILTER('fact_NCP_Reporting',
                'fact_NCP_Reporting'[NCP]="NCP"))
                ,

        (

         SUMX(

            FILTER(

                fact_NCP_Reporting,

                fact_NCP_Reporting[NCP]="Sales Order"

                ),

            fact_NCP_Reporting[SQM]

            )

            /1000

        )

    )

5.The results of this section are as follows:

6.With your date column requirements, my metrics are updated as follows:

Measure 4 = 

DIVIDE(

         CALCULATE(
            DISTINCTCOUNT(
                fact_NCP_Reporting[Sales Order No]),
                FILTER(ALLSELECTED('fact_NCP_Reporting'),
                'fact_NCP_Reporting'[NCP]="NCP"&&'fact_NCP_Reporting'[month]=MAX('fact_NCP_Reporting'[month])))
                ,

        (

         SUMX(

            FILTER(

                ALLSELECTED(fact_NCP_Reporting),

                fact_NCP_Reporting[NCP]="Sales Order"&&'fact_NCP_Reporting'[month]=MAX('fact_NCP_Reporting'[month])

                ),

            fact_NCP_Reporting[SQM]

            )

            /1000

        )

    )

7.The end result is as follows:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.