cancel
Showing results for 
Search instead for 
Did you mean: 
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 ])
)
)

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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.

View solution in original post

@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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!