Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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,
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
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,
I'll try and recreate something to demonstrate and upload it - thanks. Bear with me
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.
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,
Thankyou so much for your help. I'm glad it was something simple that I was missing!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |