How to calculate distinct count and sum of values

[BhoomiT]

while working on a project, I am facing issue in calculating distict count of a calculated measure.

The sample data is as mention below.

2017_value     2018_value    2019_value    2020_value    2021_value     Dealer_code     2019_left_dealer     2019_new_dealer

2292               4859                1219             4048              2635                 001D000133         0                            0

4292               1926                 2879            4966              2511                 001D000143         0                            0

956                 1879                3449            2477               2853                 001D000161         0                            0

0                       0                    4624           2019                3897                 001D000167         0                            1

4779              3355                1798            1788               3863                  002D000018         0                            0

1633              3805                3957           4117                3949                  002D000029         0                            0

986                741                  4012          1429                1799                   002D000039         0                            0

1707              674                 2798          3045                 4164                   002D000048         0                            0

2035              4720               4251          983                   2923                   002D000061         0                            0

4341             1564                554           1005                 1791                    002D000084         0                            0

2789             4861                  0               0                        0                       002D000165         1                            0

1388             4258                1637         1599                 3229                    002D000268          0                           0

890               2632                2929          2521                 950                     002D000295          0                           0

0                    0                    4646          4815                1107                    002D000296           0                          1

501            3238                3268            4466                2391                    002D000298           0                          0

3125          4242                2365            3851               1516                     002D000308           0                          0

3445           2743               2314            3477                4766                    002D000318           0                          0

2727          3916                3208            4409                 719                     002D000326           0                          0

Calculated 2019_left_dealer as :IF(AND(AND(CUBE_INVOICE[2021] =0 && CUBE_INVOICE[2020] =0,CUBE_INVOICE[2019] =0),CUBE_INVOICE[2018] >0),1,0)

2019_new_dealer : IF(AND(AND(CUBE_INVOICE[2021] =0 && CUBE_INVOICE[2020] =0,CUBE_INVOICE[2019] =0),CUBE_INVOICE[2018] >0),1,0)

Wanted to calculate

1) count of dealers who left and added in the network in 2019

2) sum of 2018 value for those dealers who left in 2019 and

3) sum of 2019 value for those dealers who newly added in the network request everyone to please help me to do above calculations.

[v-zhangti]

Hi, @BhoomiT 

1. For your first question, you can try the following.

Create two metrics, 2019_left Count for the number of dealers leaving in 2019 and 2019_new Count for the number of new dealers joining in 2019.

2019_left Count =
COUNTROWS ( FILTER ( CUBE_INVOICE, CUBE_INVOICE[2019_left_dealer] = 1 ) )

2019_new Count =
COUNTROWS ( FILTER ( CUBE_INVOICE, CUBE_INVOICE[2019_new_dealer] = 1 ) )

The results of the measures  values are shown in the figure.

2. For your second question, create a measure where 2019_left_2018Sum represents the sum of the 2018 values of the dealers who left in 2019.

2019_left_2018Sum =
CALCULATE (
    SUM ( CUBE_INVOICE[2018] ),
    FILTER ( CUBE_INVOICE, CUBE_INVOICE[2019_left_dealer] = 1 )
)

The results are shown in the figure.

3. For your third question, create ameasure, 2019_new_2019Sum representing the sum of the 2019 values of the new distributors joining in 2019.

2019_new_2019Sum =
CALCULATE (
    SUM ( CUBE_INVOICE[2019] ),
    FILTER ( CUBE_INVOICE, CUBE_INVOICE[2019_new_dealer] = 1 )
)

The results are shown in the figure.

Best Regards,

Charlotte Zhang

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

[BhoomiT]

Thank you for your suggesion on a problem.

The data is at invoice level for each dealer which means that for single there there is multiple rows. While calculating count of dealers it is calculating count of rows for individual dealer code. what required is distinct count of each dealer. I have tried it in multiple ways but it is not working. 

for sum of quantity, it is considering the total quantity instade of applying filters. 

In this case both the above filetr has some minor issue which I am not able to understand.

If possible please revert

[v-zhangti]

Hi,@BhoomiT

I'm sorry to get back to you so late.

For a single dealer with multiple rows, try using the SUM and COUNT functions to sum or count. Different counts for each dealer may require you to add some qualifiers to the function.

It is better if you can provide a simplified PBIX file. Please exclude the sensitive information before share these information. Thank you.

Best Regards,

Charlotte Zhang