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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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