Having a value from a column constantly on a visual and then using a slicer to add more data

[lukeSDM]

For reference my data roughly looks like this :

School	%SEN	%FSM	%overall absence
school1	1	4	10
school2	2	5	11
school3	3	6	12
school4	4	7	13
National	3.2	5.8	12.6

I would like to create a visual for each of the columns (excluding the school name) and on the visual I would like to always have the national data on there and then use a slicer to select the school that the user would like to compare with the national data.

I have tried using the filter pane on the visula but that does not seem to work.

Any help would be appreciated.

@AllisonKennedy 

[v-alq-msft]

Hi, @lukeSDM 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

School(a calculated table):

School = 
FILTER(
    DISTINCT('Table'[School]),
    CONTAINSSTRINGEXACT([School],"school")
)

You may create a measure as below.

Visual Control = 
var _school = SELECTEDVALUE('Table'[School])
return
IF(
    _school = "National",
    1,
    IF(
        ISFILTERED(School[School]),
        IF(
            _school in DISTINCT(School[School]),
            1,
            0
        ),
        0
    )
)

Then you need to put the measure in the visual level filter and use the 'school' column from 'School' table to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukeSDM]

Hi @v-alq-msft ,

is there any way to get you r solution to work in a graph rather than a matrix? 

I have tried myself to get it to work in a bar graph and when no shcool is selected the national data show but when I select a school from the slicer no data is displayed at all...

Any help would be appreciated 

Thanks

[v-alq-msft]

Hi, @lukeSDM 

I tested with 'Clustered bar chart'and it worked properly. The pbix file is attached in the end.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukeSDM]

Hi @v-alq-msft ,

When i put the post up i forgot to add that the data has a year column and I think this is where the issue is, I downloaded your file and changed it so it is more representative of my data and that is when I have an issue when creating the measure would it be a case of filtering for the year for the national?

Please your document attached with the changes.

I really appreciate your help and look forward to your response!

Thanks

https://drive.google.com/file/d/1iFCduVFnUTdCaimnKU3mX5qGLmas3QaE/view?usp=sharing

[v-alq-msft]

Hi, @lukeSDM 

You need to use the 'school' column from 'School' table to filter the result instead of the 'school' column from 'Sheet1' table. The pbix file is attached in the end.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukeSDM]

@v-alq-msft 

Hi,

This still does not work as I realised in the DAX it filters it to contain "school" but in reality all the school names are different i need to it to exclude the National from the school column really is there a dax expression for that?

Thanks

[v-alq-msft]

Hi, @lukeSDM 

You may try the following dax to get a table excluding 'National'.

Tab = 
FILTER(
    DISTINCT('Table'[School]),
    'Table'[School]<>"National"
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lukeSDM , you can have sum and Avg with all

calculate(sum(Table[%SEN]),all(Table])

There is an option like removefilters. Refer to my blog to check that

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

There s pbix with the blog to get ALL, remove filters usages

[AllisonKennedy]

I have just suggested this formula for a similar problem:
ShowColumns =
Var SlicerSelection = VALUES(Slicer[Select])
Var AlwaysShow = {("A1"), ("A2"), ("A3")}
Var ShowValues = UNION(SlicerSelection, AlwaysShow)
RETURN
IF(SELECTEDVALUE('Table'[Attribute]) IN ShowValues, 1, 0)
https://community.powerbi.com/t5/Desktop/How-could-implement-selecting-columns-which-shows-in-table/...

So for your scenario would require having a table of schools that is not connected to the rest of the data model and using "National" instead of ("A1"), ("A2"), ("A3")

Let us know if you need more details.

[Pragati11]

Hi @lukeSDM ,

Create a measure for your National level metric something as follows so it doesn't chnage with the slicers:

%SEN National = CALCULATE(SUM(Table1[%SEN]), ALL(Table1))

Similary, create for other columns as well. You can modify your summarisation in above DAX as per the requirement.

Then move these national figures, say, to a visual along with the columns from your table.

Whenever you select a value from a slicer, these National level measures won't change, but your other values will change.

Thanks,

Pragati

[lukeSDM]

It seems when doing this it adds all the values in that column and does not filter to the national data ?

[Pragati11]

HI @lukeSDM ,

Please use dax as follows:

%SEN National = CALCULATE(SUM(Table1[%SEN]), ALL(Table1), Table1[School] = "National")

Thanks,

Pragati

[AllisonKennedy]

@lukeSDM to use @Pragati11 's solution you will need to modify it a bit, as others are assuming that national is a sum or average of the rows above. To get just the national data, try:

%SEN National = CALCULATE(SUM(Table1[%SEN]), ALL(Table1), Table1[School] = "National")

[lukeSDM]

@AllisonKennedy @Pragati11 

Uing Pragati11 method the only way I can get it display is by using the line and column visual.

My issues with this are: 

The national data is for 2018/2019 so its over the wrong column 

I would prefer it to be a column next to the school selected from the slicer 

See picture attached:

it would be good if i could get the dot over the 2018/2019 column can this be done?

[Pragati11]

Hi @lukeSDM ,

I am not clear on your chart. What value have to moved to x-axis?

Can you share screesnhots of details on this chart from visualisation pane?

Also, the sample data which you shared earlier, hasn't got any DATE column in it. Try sharing your exact sample data.

Is it possible to share your pbix file?

Thanks,

Pragati

[lukeSDM]

@Pragati11 @AllisonKennedy 

Sorry I tried to make the data a bit easier to understand.

if you treat the URN as the school name and then keep the same columns I orginally said ill be able to match them up to my table.

the national data is only for 2018/2019 but school data is 2016/17,2017/18,2018/19

[Pragati11]

Hi @lukeSDM ,

Can you show some screesnhots on what's on working and how you are applying the above solution?

Thanks,

Pragati