cancel
Showing results for
Did you mean:
Highlighted
New Member

## Use the Calculate formula by a Distinct Count from the Sum of another Column in the Table

So I want to use the calculate function but I'm really struggling with getting it to work in this instance. From the table below, I want to identify the number unique of customers whose sum of quantities of a certain product come to zero.

From the table below as an example, the DAX formula should come back with 3 customers because they didn't buy a particular product from Range 2. Hope that makes sense, all help much appreciated. 1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User III

## Re: Use the Calculate formula by a Distinct Count from the Sum of another Column in the Table

Hi @Amos1992 ,

Try the following measure:

``````Measure =
VAR Total_Quantity =
SUMMARIZE (
'Table',
'Table'[Customer Type],
'Table'[Product],
'Table'[Product Range],
"Quantity Total", SUM ( 'Table'[Quantity] )
)
var ttt = SUM('Table'[Quantity])
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer Type] ),
FILTER ( Total_Quantity, [Quantity Total] = 0 )
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português Highlighted Super User III

## Re: Use the Calculate formula by a Distinct Count from the Sum of another Column in the Table

Hi @Amos1992 ,

Try the following measure:

``````Measure =
VAR Total_Quantity =
SUMMARIZE (
'Table',
'Table'[Customer Type],
'Table'[Product],
'Table'[Product Range],
"Quantity Total", SUM ( 'Table'[Quantity] )
)
var ttt = SUM('Table'[Quantity])
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Customer Type] ),
FILTER ( Total_Quantity, [Quantity Total] = 0 )
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português Announcements #### Get Ready for Power BI Dev Camp #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más! Top Solution Authors
Top Kudoed Authors
Users online (1,930)