Line charts with multiple series dissociated

[Anonymous]

Hi,

I have a table with real estate projects. My client A wants a chart with 3 lines that show :

- how many projects by year

- how many projects made by my client A by year

- how many projects made by a specific competitor selected through a segment

My measures :

#how_many_projects = DISTINCTCOUNT(PROJETS_LOGEMENT[ID_PROJET_CONCAT])

#how_many_projects_companyA = CALCULATE([#how_many_projects];FILTER(PROJETS_LOGEMENT;PROJETS_LOGEMENT[companyA]=1))

#how_many_projects_competitor = CALCULATE([#how_many_projects];FILTER(PROJETS_LOGEMENT;PROJETS_LOGEMENT[COMPANY]=[Selected competitor for comparison]))

Selected competitor for comparison = SELECTEDVALUE(PROJETS_LOGEMENT[COMPANY])

My problem : 

1) I want that my segment competitor only affects the measure #how_many_projects_competitor

when I select a competitor, it "destroys" the chart. I tried to use ALL or ALLSELECTED.....in vain

any idea how I can dissociate those measures ?

many thanks !

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create measures using following formulas:

how_many_projects = 
CALCULATE (
    DISTINCTCOUNT ( 'PROJETS_LOGEMENT'[ID_PROJET_CONCAT] ),
    REMOVEFILTERS('PROJETS_LOGEMENT'[COMPANY])
)

how_many_projects_companyA = 
CALCULATE (
    DISTINCTCOUNT ( PROJETS_LOGEMENT[ID_PROJET_CONCAT] ),
    REMOVEFILTERS ( 'PROJETS_LOGEMENT'[COMPANY] ),
    PROJETS_LOGEMENT[companyA] = 1
)

how_many_projects_competitor = 
CALCULATE (
    DISTINCTCOUNT ( 'PROJETS_LOGEMENT'[ID_PROJET_CONCAT] ),
    FILTER (
        PROJETS_LOGEMENT,
        PROJETS_LOGEMENT[COMPANY] = [Selected competitor for comparison]
    )
)

Then we create a year table to prevent the x axis be slicered by the comparison colmun.

Year = DISTINCT('PROJETS_LOGEMENT'[Year])

If it doesn't meet your requirement, Please show the exact expected result based on the my shared sample tables.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-lid-msft 

thank you so much, you're a master !

I've already had a table with the year like in your example. I replaced my formulas by yours and it works great. I can compare the results of my client A with a client B and with the overall clients.

I didn't know the function REMOVEFILTERS