cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nantes44100 Regular Visitor
Regular Visitor

Line charts with multiple series dissociated

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

 

2019-11-07_10h40_15.png

 

any idea how I can dissociate those measures ?

 

many thanks !

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Line charts with multiple series dissociated

Hi @Nantes44100 ,

 

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])

 

10.PNG7.PNG8.PNG9.PNG

 


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.

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Line charts with multiple series dissociated

Hi @Nantes44100 ,

 

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])

 

10.PNG7.PNG8.PNG9.PNG

 


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.

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.

View solution in original post

Highlighted
Nantes44100 Regular Visitor
Regular Visitor

Re: Line charts with multiple series dissociated

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)