cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
royb1973 Frequent Visitor
Frequent Visitor

Number of unique partners with x revenue (countif)

Hi there, I am just a starting PowerBi user and I have a question about how to calculate the unique number of partners with more than 10k revenue.
Model:
Table "Products", with columns [ProductID], [ProductGroup] and more
Table "Invoicelines" , with columns [ProductID], [Revenue], [InvoiceID]
Table "Invoiceheader", with columns [InvoiceID], [PartnerID], [Date]
Table "Date", with all kind of Datecolumns like Period, Quarter, Dayindex, etc.
Table "Target" with columns [Quarter], [Target]
The tables are linked. 

From one of our vendors, we receive 6 different targets. 
I already managed to make 5 targets and the realization of them visible in PowerBi. But with one I am struggling. 
Target: X Partners with a minimum of 25k revenue a quarter.

Now I have made a visual with a matrix, where the first field is the period [quarter] and the second field is the target.
I need a third field which has a measure with the number of unique partners with more than 25k revenue, where the product group contains "DT" (products in these product groups (DT5460, DT4310 etc) are of this specific vendor). And I hope that because the period is in the Matrix the measure will apply to the period. 
Can somebody please help me where to start.

Thank upfront. Roy.

 

1 ACCEPTED SOLUTION

Accepted Solutions
royb1973 Frequent Visitor
Frequent Visitor

Re: Number of unique partners with x revenue (countif)

Thnx @v-xuding-msft .

 

I have manged it now by merging the two tables and filter only the right productgroups.

Then I aggregate the table by CustomerID and Period with a sum on the revenue.

With the DISTINCTCOUNT on the CustormerID I had the right result.

 

Greats Roy

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Number of unique partners with x revenue (countif)

Hi @royb1973 ,

Based on your description, I created a sample to calculate it. You can try and check if it is what you want.

Calculated column :
DT = IF(ISERROR(SEARCH("DT",Invoicelines[ProductID])),0,999)

Measure:
Measure = CALCULATE(DISTINCTCOUNT(Invoiceheader[PartnerID]),FILTER(Invoicelines,Invoicelines[Revenue] >=25000 && Invoicelines[DT] = 999))

6.PNG

I attached my sample that you can download. If it is not what you want, please share some sample data and your expected output. Then we will understand clearly and solve it quickly.

How to Get Your Question Answered Quickly

 

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. Kudos are nice too.

royb1973 Frequent Visitor
Frequent Visitor

Re: Number of unique partners with x revenue (countif)

Hi @v-xuding-msft  thnx so far.

Let me try to make it work. I see some challenges (because there a  lot of invoicelines and I need the sum of it for that quarter.

But let me try, you pushed me in the right direction.

 

Thnx. Greets Roy

 

 

royb1973 Frequent Visitor
Frequent Visitor

Re: Number of unique partners with x revenue (countif)

Thnx @v-xuding-msft .

 

I have manged it now by merging the two tables and filter only the right productgroups.

Then I aggregate the table by CustomerID and Period with a sum on the revenue.

With the DISTINCTCOUNT on the CustormerID I had the right result.

 

Greats Roy

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,006)