Measure that will be filtered from many to 1 relationship

[Anonymous]

Dear All

I'attached my model.

• The table "Booking profile" contains all the coming orders for my sales organisation : Sales stage, % of 
• The table "Opportunities with BLI" will contain the details of the orders : Business unit, Amount, qunatity .... 
• The table "Opportunities with STM" contains the sales teams working on each opportunity and the reole they play
• The table LCL Userr ID is a di;emsion table contaiing the list of all sales people.
• date table

the other tables are not relevant to my concern

I would lie to create a measure that allow me to know the amount of won opportunites. Sales stage (field in Booking profile" table tells me which opportunites are won and the amount by Business units is present in the "Opportunities with BLI" I need this measure to be fltered through slicers by Business unit, By sales team member, by date

I am using power pivot not BI otherwise it will simple by using the birectional filtering

please support

Regards,

Imbrg

[v-yuezhe-msft]

@Anonymous,

In Power BI Desktop, you are able to establish birectional relationship between tables.


Could you please share sample data of your tables and post expected result based on sample data here? You can follow  the guide in this blog to provide sample data.

Regards,
Lydia

[AlB]

Hi @Anonymous

If  I understand correctly, you'd need to filter with expanded tables. 

Have a look at this and at this

[Anonymous]

Hi AIB

thank you for you quick answer.

I still do not understant how can I solve this proble as I am beginner in dax.

hat I would like to achieve is a measure that calculates won Opportunities and can slice that bases on a particular team member (from Opportunites with STM)

regards

[AlB]

Hi @Anonymous

Please provide details for one of the measures that you need: fields involved and what tables they are in, plus relationships between those tables. We'll take it from there.

If you can share the excel file you're working with (or a sample of it) it'd make things easier

[Anonymous]

Hello,

Please find below the link for the excel file I am workng on

I've created a measure that calculates the won opportunities based on the sales stage. It sums the amounts from the booking details table

I inserted two slicers : Name of the sales team members and Business units.

I filters when using the slicer Business unit but not the name of the sales team member.

Please advide

thank you for your help

https://laurentide0-my.sharepoint.com/:x:/g/personal/ibourega_laurentide_com/EVMldwoOdqFGpwr-_j4QGMI...

regards,

[Ashish_Mathur]

Hi,

The Pivot Table does not respond to a selection in the Name slicer simply because there is no User ID column in the Booking Table.  There should be a user ID column there with a relationship to the Users Table.

[Anonymous]

Hi Ashish

Thank you for your quick answer

I cannot have user id in the table booking as for a particular booking I can have many sales team members working on it.

and this is my problem

How can I know for a particular team member which opportunites were won and he was involved.

thanks 

[Ashish_Mathur]

That's a crucial missing piece of information.  We musy have that to all UserID wise slicing.

[Anonymous]

I did not understand you answer. we cannot have this kind of filter?

[Ashish_Mathur]

No, you cannot.  Think about it - you are trying to filter your data by a column which bears no relationship (direct or indirect) with the Billing Table.

[Anonymous]

There is a relationship between the booking table (one side)  and the STM which is the sales teams mumber (many sides)

[Ashish_Mathur]

Hi,

I think i can solve this.  I need the 3 Excel files - Booking, STM and Booking details.  Share just one Excel file with these 3 tables.

[Anonymous]

thanks Ashish for your support

here is the link

https://laurentide0-my.sharepoint.com/:x:/g/personal/ibourega_laurentide_com/Ef4hl5EZ8tlEiA8_k2li23M...

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution workbook from here.

Hope this helps.

[Anonymous]

Hi Ashish that's working fine. The idea was to merge the two tables STM and Booking details right?

thanks 

[Anonymous]

Ashish,

The total pipeline is not good as there is duplicates

I write the followinf measure but it's not working

Pipeline:=SUMX(FILTER('Booking details and STM', DISTINCT('Booking details and STM'[Unique Opportunity ID])),[Amount])

can you please support

thanks

[Ashish_Mathur]

Hi,

Right click on Booking details and STM Table and click on Edit.  Select all columns, right click and Remove Duplicates.  Click on Close and Load.  Do you now get the correct results?

[Anonymous]

Thanks it's working

[Ashish_Mathur]

You are welcome.