cancel
Showing results for
Did you mean:  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: 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  Community Support

Hi @o59393 ,

``````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_ )
`````` 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.
7 REPLIES 7  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 ])
)
)

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!  Post Prodigy

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!  Post Prodigy

Here is the excel with the desired result in yellow

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

Regards.  Community Support

Hi @o59393 ,

``````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_ )
`````` 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.  Post Prodigy

@v-xuding-msft  Awesome, thanks!  Super User

Don't use EARLIER(). Use variables.

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

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.  