Recalculate summarised table based on slicer

[Anonymous]

Following on from this topic https://community.powerbi.com/t5/Desktop/Help-with-data-conversion/m-p/639339#M306095

Background: 

I have captured a number of records of "Expectations" which are grouped by "SIFS" from different "Sites" and each expectation can be either Compliant, N/A or Non-Compliant.

So far I have calculated the % Non-Compliant by

I created a summarised table "Expectations" from the unpivoted table "Table1Unpiv"

I have then linked the tables like so

I put this into a table visualization and a slicer based "SIF"S in Table1unpiv which will then filter the rows of the the table visualisation

What I want to do is use another slicer based on "Sites" 

I want this slicer to select only the entires from each site and therefore change the number of entries against each expectation and then recalculate the % Non-Compliance

I can;t work out how to do this

Any help would be greatly appreciated

[v-jiascu-msft]

Hi @Anonymous ,

They are from the same table. The "SIFS" and the "Sites" will filter each other. So you can select only the entries from each site. 

What are the issues?

Can you share a sample? Please mask the sensitive parts first.

Best Regards,

[Anonymous]

Hi, 

I will be struggling to share the .pbix I'm afraid

Yes the SIFS, SITES and EXPECTATIONS are all in the same table

If I filter the EXPECTATIONS by SIFS, it works because the EXPECTATIONS are unique within the SIFS, so the total number of rows counted in the summarized table does not change, and only the filtered EXPECTATIONS are shown

If I filter by SITES then the visible EXPECTATIONS do not change as they are not unique to the SITES and the counts from the summarized table do not change either, so regardless of the filter the counts are the same

As a workaround, 

I created a measure in the Table1Unpiv table which calculates based upon the slected value in a new table called SITES which contains a unique list of each SITE

e.g. 

Measure

Site Observations = calculate(countrows(Table1Unpiv),filter(Table1Unpiv,Table1Unpiv[Site]=selectedvalue(Sites[Site])))

Unique Table

Sites = distinct(Table1Unpiv[Site])

This gives nearly the desired output but only allows the selection of one SITE at a time

[v-jiascu-msft]

Hi @Anonymous ,

It sounds strange. Please share the file if you can. Only a little sample that can reproduce the issue is needed. Please mask the sensitive parts first.

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

Best Regards,

[Anonymous]

I'll try and recreate something to demonstrate and upload it - thanks. Bear with me

[Anonymous]

I have tried to recreate in the linked file

https://1drv.ms/u/s!AjUCCXTPyb7ubDow0kHB7OfTMS8

Summary

Data represents audits carried out over three sites, Liverpool, London and Manchester

Each audit focusses on one category called SIFS, LTXX, MGXX, PTXX, SLXX

Each SIFS has unique expectations that are checked, LT01, LT02, MG01, MG02 etc.

1. Data has been imported to the Audits table. The individual expectations columns have been Unpivoted and the resulting new columns have been title Expectation and Result

2. A summarised table has been created called, Expectations

3. The tables Audits and Expectations have been linked by the Expectation Column with "both" as the cross filter direction

4. In the example Tab of Reports the problem can be seen. We are simply looking at a count of the number of observations

I can slice by SIFS and only the relevant expectations are shown

If I slice by site

London has had no MGXX SIFS audits so they don't show which is fine

But, there have been a total of 77 checks against expectation LT01 (38 Liverpool, 21 London, 18 Manchester)

Selecting the site in the slicer does not effect the Sum of observations shown

As a workaround

1. Create a distinct table called UniqueSites that lists each site

2. In the Expectations table create a measure to count the filtered rows in Audits[Site] based on the selected value in UniqueSites(Site)

MeasureObservations = calculate(countrows(Audits),filter(audits,Audits[Site]=selectedvalue(UniqueSites[Site])))

3. This is shown in Reports, Workaround Tab

This gives the desired behavior but can only work when a single site is selected so doesn't give the kind of drill down I want unless I end up with two tables

I hope that makes sense. I am very new to this so hopefully I am missing something obvious.

Thanks.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

After looking into your file, I wonder why you created a calculated table. Actually, it isn't necessary. All the results can be easily achieved by measures. Please download the demo from the attachment.

Regarding the calculated table, the granularity is too big. The Observation value 110 can't be 44 for London. Because it's a static value in the table. 

Compliant =
CALCULATE ( COUNTROWS ( Audits ), Audits[Result] = "" )

Best Regards,

[Anonymous]

Thankyou so much for your help. I'm glad it was something simple that I was missing!