How to divide with distinct count depending on the combination of 2 columns

[o59393]

Hi all

I need a fix on the denominator of my calculated column in order to divide properly in function of the distinct count of monthrs for the combination BPP Code & Bottler.

The formula is:

Average = 
CALCULATE( 
    SUM ( 'Sheet1'[Mix]),
        FILTER ( 'Sheet1',
            [[ BPP Code ]]] = EARLIER ( [[ BPP Code ]]] )
                && 'Sheet1'[[L1.2 - Bottler]]]
                    = EARLIER ( 'Sheet1'[[L1.2 - Bottler]]] ) 
                        && 'Sheet1'[[ BU Ship From ]]] 
                            = EARLIER( 'Sheet1'[[ BU Ship From ]]])
        )
)
/
DISTINCTCOUNT('Sheet1'[[Month 445]]])

For example the BPP code "232031405" for Bottler "AB" has data for 7 months. However the current formula is dividing by 8.

Why 8? Because the total months for exisiting bottlers is 8:

However, "AB" has data for 7 months and bottler "FCR" just data for 4 months.

The correct way to do it would be to divide only by the distinct months for the combination bottler+code, as stated above.

How can I fix this?

Pbix

https://1drv.ms/u/s!ApgeWwGTKtFdhynS9xpgFReudUKP?e=BgBQlU

Thanks!

[amitchandak]

@o59393 , Try like

Average =
divide(
CALCULATE(
SUM ( 'Sheet1'[Mix]),
FILTER ( 'Sheet1',
[ BPP Code ] = EARLIER ( [ BPP Code ] )
&& 'Sheet1'[[L1.2 - Bottler]]]
= EARLIER ( 'Sheet1'[L1.2 - Bottler] )
&& 'Sheet1'[ BU Ship From ]
= EARLIER( 'Sheet1'[ BU Ship From ])
)
)
,
DISTINCTCOUNT('Sheet1'[Month 445]))

or

Average =
CALCULATE(
divide(SUM ( 'Sheet1'[Mix]),
DISTINCTCOUNT('Sheet1'[Month 445])),
FILTER ( 'Sheet1',
[ BPP Code ] = EARLIER ( [ BPP Code ] )
&& 'Sheet1'[[L1.2 - Bottler]]]
= EARLIER ( 'Sheet1'[L1.2 - Bottler] )
&& 'Sheet1'[ BU Ship From ]
= EARLIER( 'Sheet1'[ BU Ship From ])
)
)

[o59393]

Hi @amitchandak 

I used your second dax but got a different result to the expected which is in yellow:

The dax used was:

Average = 
CALCULATE(
    DIVIDE(
    SUM ('Sheet1'[Mix]), DISTINCTCOUNT('Sheet1'[[Month 445]]])),
        FILTER ( 'Sheet1',
            [[ BPP Code ]]] = EARLIER ( [[ BPP Code ]]] )
                && 'Sheet1'[[L1.2 - Bottler]]]
                    = EARLIER ( 'Sheet1'[[L1.2 - Bottler]]] ) 
                        && 'Sheet1'[[ BU Ship From ]]] 
                            = EARLIER( 'Sheet1'[[ BU Ship From ]]])
        )
)

The result:

For example OPL Zacapa is giving 2.05% in PBI but should be 0.88% as stated in the first image.

Thanks!

[o59393]

Here is the excel with the desired result in yellow

https://1drv.ms/x/s!ApgeWwGTKtFdhyju4mjDUCshNPDw?e=sF3zFd

Regards.

[v-xuding-msft · Accepted solution]

Hi @o59393 ,

Please try this:

Average =
VAR mix_ =
    CALCULATE (
        SUM ( 'Sheet1'[Mix] ),
        FILTER (
            'Sheet1',
            [[ BPP Code ]]] = EARLIER ( [[ BPP Code ]]] )
                && 'Sheet1'[[L1.2 - Bottler]]] = EARLIER ( 'Sheet1'[[L1.2 - Bottler]]] )
                && 'Sheet1'[[ BU Ship From ]]] = EARLIER ( 'Sheet1'[[ BU Ship From ]]] )
        )
    )
VAR month_ =
    CALCULATE (
        DISTINCTCOUNT ( 'Sheet1'[[Month 445]]] ),
        ALLEXCEPT ( Sheet1, Sheet1[[L1.2 - Bottler]]] )
    )
RETURN
    DIVIDE ( mix_, month_ )

[o59393]

@v-xuding-msft  Awesome, thanks!

[lbendlin]

Don't use EARLIER(). Use variables.

Move the DISTINCTCOUNT() inside the CALCULATE so that it is affected by the desired filters.

[o59393]

Hi @lbendlin 

Don't know how to use variables instead of earlier. I did move the distinct count inside calculate but didnt give me the result desired:

Average = 
CALCULATE(
    DIVIDE(
    SUM ('Sheet1'[Mix]), DISTINCTCOUNT('Sheet1'[[Month 445]]])),
        FILTER ( 'Sheet1',
            [[ BPP Code ]]] = EARLIER ( [[ BPP Code ]]] )
                && 'Sheet1'[[L1.2 - Bottler]]]
                    = EARLIER ( 'Sheet1'[[L1.2 - Bottler]]] ) 
                        && 'Sheet1'[[ BU Ship From ]]] 
                            = EARLIER( 'Sheet1'[[ BU Ship From ]]])
        )
)

Thanks.