Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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"

 

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

    Recalculate-summarised-table-based-on-slicer

     

     

    Best Regards,

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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,