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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zguan0923
Frequent Visitor

sumif and filter on two different tables

Hello!

 

I am new to Power BI and would really appreciate you help on the following question:

 

I have two tables: Registration Status (Approved, Declined, In Process), and Pipeline (Projected Sales Amount). I am looking for sum of sales amount for each registration status. Thank you!

 

Registration Status 
Opp IDRegistration Status
123456Approved 
234567Approved 
345678Approved 
456789Approved 
654321Declined
765432Declined
876543In Process 
987654In Process 

 

Pipeline 
Opp IDAmount
123456500
234567600
345678700
456789800
654321900
765432100
876543300
987654200
8 REPLIES 8
ceebu
Advocate I
Advocate I

WHen I tried using the same data, PowerBI automatically detected the relationships between these two tables.  The resulting table provides what you want (right hand side)

Screenshot - 4_7_2017 , 1_56_28 PM.png

 

 

Thank you Ceebu. I realized what the problem was. The amount field wasn't a direct value in the pipeline table, but a key measure based on a calculation of two fields in the pipeline table. It seems I can't manage the relationships between Registration Table and Key Measures, and when I try to drag amount to different registration status, the amount shows as a lump sum value of all the registration status rather than individual break down of each status. 

 

What should I do in this case?  Thank you!

I am not able to understand the requirement clearly - If you could securely share the sample data, I can try to find a way.

Hi Ceebu!

 

Thank you. I managed to solve the problem on linking the data thanks to you suggestion. 

 

I have a follow up question: now that I have created a calculated field of registration count for each of the status under registration status field (approved, declined, in process). I would like to add a  "submitted" status to my table, which is the sum of current 3 status. I was wondering what would be the best approach to do this?

 

Thanks!

Hi @zguan0923,

 

I'd like to suggest you write a formula to get the distinct count of the state of current item, if it equal to 3, then return "submitted".

 

Sample formula:

Submit State = IF(CALCULATE(DISTINCTCOUNT(Table[Registration Status]),FILTER(ALL(Table),[Opp ID]=MAX([Opp ID])))=3,"submitted","not submitted")

 

 

Regards.

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you Xiaoxin.

 

I have tried your recommended approach and received the following error msg: "too many arguments were passed to the MAX function. The maximum argument count for the function is 2."

 

To give you a bit more background - "Submitted" does not currently exist in the data table. I need to 1) create a submitted field as one of the options under Registration Status, and 2) calculate the the registration count for submited (sum of the other registration status - approved, denied, in process) . I hope this makes sense. Thank you!

 

Table 1   Current Output 
Opp IDRegistration Status  Registraion StatusRegistration Count
123456Approved  Approved4
234567Approved  Denied 4
345678Approved  In Process 2
456789Approved    
987654Denied     
876543Denied   Target Output 
765432Denied   Registraion StatusRegistration Count
654321Denied   Approved4
135790In Process   Denied 4
975310In Process   In Process 2
    Submitted10

Hi @zguan0923,

 

You can try to use below formula to get the output table.

 

Output = UNION(SUMMARIZE(Sheet2,[Registration Status],"Count",COUNT(Sheet2[Registration Status])),ROW("Registration Status","Submitted","Count",COUNTROWS(Sheet2)))

Capture.PNG

 

 

Notice: As you said, current "submitted" row not real contains in the table, so I use the union and row function to add them to the merged table.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you! It's working now! Very helpful!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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