cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 166 members 1,503 guests
Please welcome our newest community members: