problem finding visits that I have in common with my competitors

[ndamota]

Hello everybody,

I have a problem finding visits that I have in common with my competitors

I have a table or I have Mandates, allotted to Mandates (Commercial) companies.

The folowing exemple :

Mandat	Société Mandat	Visite Société
M1	Total	5
M2	Total	3
M3	Total	2
M1	Auchan	5
M2	Auchan	2
M5	Auchan	1
M8	Total	1

I can do calculate this

include a percentage that represents the total number of visits by the company to its joint mandates divided by the total number of visits made by all competitors to the joint mandates.

My goals  its example

If I filter by company of total. I have 8 visites of my mandat (8+7 visite compétitors on common)

The results its

8/15 donne 53 %.

I try this measurement but the approximate results

#Nb de visite en commun = Var A = VALUES(Mandat)

return

CALCULATE(SUM(Visites Société);ALL(Société Mandat]);A)

Measure 3= Sum(Visites Société)/ #Nb de visite en commun

My problem is that if I have an M8 mandate which is unique. And I select Total as a filter. The final value will be 9/16
How can you do to say to be able to select me as those where I am in common.

Has anyone ever encountered this problem, and comment on the resolution please?

Thank you

[amitchandak · Accepted solution]

Please check, if this can work for you. https://www.dropbox.com/s/p2w0w205dg8ny3w/visitscompetitors.pbix?dl=0

[v-diye-msft · Accepted solution]

Hi @ndamota 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[Greg_Deckler]

I can't figure out where you are getting your 8 and 15 numbers from. 

[ndamota]

Hi

thank you for answer. Sorry my english is bad

If I take in panel filter Company "total ". I first Measure would calculate the visits for company "Total" with paramenter common of mandat with other company. Secondly I would the visit common with M1 and M2 of companys other of "Total"

I get 8 , if I groupe by colunm  Société Mandat " total" and sum the visit by common.

Mandat	Société Mandat	Visite Société
M1	Total	5
M2	Total	3

            Measure 1=                                                                          8

Measure 1 = Sum ( visit)

I get 7 visits who common with Mandat (M1; M2) of common with Mandat of "Total" 

Mandat	Société Mandat	Visite Société
M1	Auchan	5
M2	Auchan	2

                                        Measure 2                                              7

I get 8/(8+7) => My company Total Have 8/15 =53% of visite visits carried out by Total

Measure 2 =

#Nb visit common other company = Var A = VALUES(Mandat)

return

CALCULATE(SUm(visit);FILTER(ALL(Société Mandat);Société Mandat<>SELECTEDVALUE(Société Mandat));A)

Thank you, Tomorrow I send you the Pbix example

[amitchandak · Accepted solution]

Please check, if this can work for you. https://www.dropbox.com/s/p2w0w205dg8ny3w/visitscompetitors.pbix?dl=0