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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Amos1992
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.

 

Amos1992_0-1598017095419.png

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.