Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

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

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:

 

223212.JPG

 

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!

1 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_ )

v-xuding-msft_0-1598866521748.png

 

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

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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 ])
)
)

 

Hi @amitchandak 

 

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

dsfsdfdsfds3.JPG

 

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:

 

9908989.JPG

 

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

 

Thanks!

Here is the excel with the desired result in yellow

 

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

 

Regards.

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_ )

v-xuding-msft_0-1598866521748.png

 

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

@v-xuding-msft  Awesome, thanks!

lbendlin
Super User
Super User

Don't use EARLIER(). Use variables.

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

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.