Forum Discussion

skitovich's avatar
skitovich
Helper I
6 years ago
Solved

Help with graphing

Good afternoon. I would be very grateful if you help with one question. In general, there are graphs shown in the first figure. When choosing a manager, only the selected manager is shown on the slice. When choosing two managers (Fig. 2), their comparison is shown. The question is this. How to make sure that their sum is displayed, that is, one line when no manager on the slice is selected. The slice is highlighted in the photo. I will be very grateful. I rack my brains for a long time.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi,

     

    After trying some things i came up with a solution, I don't know if it's the best possible solution, but it seems to work for me.

     

    First i added a new custom column to your existing table with managers, with all values being "all"

    ifnotfiltered = "all"

     

    Then I created a new table, with all the distinct managers and an extra row for "all": 

    filtertable = UNION(CALCULATETABLE(DISTINCT('Table'[manager])); ROW("manager"; "all"))

     

    Then in the model window i created two relationships:

     - 'filtertable'[manager] > 'Table'[Manager] 

     - 'filtertable'[manager] > 'Table'[ifnotfiltered]

    Then create a measure that checks if the manager is filtered or not, if there is a filter we use the regular relationship, else we use the second relationship where the manager is "all"

    isfiltered = if(ISFILTERED('Table'[manager]); CALCULATE(SUM('Table'[value]) ; USERELATIONSHIP('Table'[manager]; filtertable[manager])) ;CALCULATE( SUM('Table'[value]); USERELATIONSHIP('Table'[ifnotfiltered]; filtertable[manager])))


    Use this measure as the Values of your charts. & it should be working.

    note: Due to my regional settings I use ' ; ' instead of ' , ' so maybe you need to change the semicolons to comma's in your measures

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

    After trying some things i came up with a solution, I don't know if it's the best possible solution, but it seems to work for me.

     

    First i added a new custom column to your existing table with managers, with all values being "all"

    ifnotfiltered = "all"

     

    Then I created a new table, with all the distinct managers and an extra row for "all": 

    filtertable = UNION(CALCULATETABLE(DISTINCT('Table'[manager])); ROW("manager"; "all"))

     

    Then in the model window i created two relationships:

     - 'filtertable'[manager] > 'Table'[Manager] 

     - 'filtertable'[manager] > 'Table'[ifnotfiltered]

    Then create a measure that checks if the manager is filtered or not, if there is a filter we use the regular relationship, else we use the second relationship where the manager is "all"

    isfiltered = if(ISFILTERED('Table'[manager]); CALCULATE(SUM('Table'[value]) ; USERELATIONSHIP('Table'[manager]; filtertable[manager])) ;CALCULATE( SUM('Table'[value]); USERELATIONSHIP('Table'[ifnotfiltered]; filtertable[manager])))


    Use this measure as the Values of your charts. & it should be working.

    note: Due to my regional settings I use ' ; ' instead of ' , ' so maybe you need to change the semicolons to comma's in your measures