cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
G_Gornall Frequent Visitor
Frequent Visitor

Recalculate summarised table based on slicer

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"

Summarized table.jpg

 

I have then linked the tables like so

link.jpg

 

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

Table visualisation 2.jpg

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Recalculate summarised table based on slicer

Hi @G_Gornall,

 

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

Recalculate-summarised-table-based-on-slicer

 

 

Best Regards,

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

Re: Recalculate summarised table based on slicer

Hi @G_Gornall ,

 

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,

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

Re: Recalculate summarised table based on slicer

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

 

 

Community Support Team
Community Support Team

Re: Recalculate summarised table based on slicer

Hi @G_Gornall ,

 

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,

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

Re: Recalculate summarised table based on slicer

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

G_Gornall Frequent Visitor
Frequent Visitor

Re: Recalculate summarised table based on slicer

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.

 

Community Support Team
Community Support Team

Re: Recalculate summarised table based on slicer

Hi @G_Gornall,

 

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

Recalculate-summarised-table-based-on-slicer

 

 

Best Regards,

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

Re: Recalculate summarised table based on slicer

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