title: List report with each line showing percentage of one specific line
body: <p>I have a Power BI report with multiple lines (it's a P and L report) displayed in a list visualisation.</p>
  
  <p>It's populated from a view in SQL</p>
  
  <p>One of the lines is 'Total Revenue'</p>
  
  <p>I need every line to display its proportion of Total Revenue as a percentage:</p>
  
  <p>i.e.</p>
  
  <pre><code>LINE           MTH     %
  ------------------------
  Line1            5    10 
  Total Revenue   50   100
  Line2           20    40
  Line3            4     8
  Line4           12    24
  </code></pre>
  
  <p>Note that <code>MTH</code> does not add up to total revenue so I can't just take the total, I need to take the line called Total Revenue as the denominator</p>
  
  <p>Here is the perfect example of what I'm trying to do here:</p>
  
  <p><a href="https://msdn.microsoft.com/en-us/library/ee634760.aspx" rel="nofollow noreferrer">https://msdn.microsoft.com/en-us/library/ee634760.aspx</a></p>
  
  <p>But when I try this method (firstly just to get the denominator), I only get a value on the Total Revenue line and blanks on the other lines, like this:</p>
  
  <pre><code>LINE           MTH     %
  ------------------------
  Line1            5    
  Total Revenue   50    50
  Line2           20    
  Line3            4    
  Line4           12    
  </code></pre>
  
  <p>Here's my DAX:</p>
  
  <pre><code>MTH% = 
    SUMX(
        CALCULATETABLE(
           'vw_PandL',
           'vw_PandL'[Line]="TOTAL REVENUE"
            )
         ,
            [MTH]
    )
  </code></pre>
  
  <p>As far as I can tell, the only difference between my expression and the example on the web page, is that the web page uses a <em>separate</em> dimension to filter the row, whereas may expression uses a column in the <em>same</em> table to filter the row</p>
  
  <p>Example Given on web page:</p>
  
  <pre><code>=SUMX( CALCULATETABLE(
          'InternetSales_USD', 
          'DateTime'[CalendarYear]=2006
       )  
   , [SalesAmount_USD])  
  </code></pre>
  
  <p>Based on comments below this should work, but it doesn't work for me</p>
  
  <p>I've now also tried:</p>
  
  <pre><code>CALCULATE(
      SUM([MTH]),
      FILTER(ALL('vw_PandL'),
          [Line]="TOTAL REVENUE")
          )
  </code></pre>
  
  <p>Which gets me the grand total on all lines for TOTAL REVENUE, but ignores report filters. This is closer.</p>
  
  <p>The  <code>ALLEXCEPT</code> function is meant to unfilter everything except the designated columns, however when I try this, I get the same result. It still doesn't take note of my date and site filters:</p>
  
  <pre><code>CALCULATE(
      SUM([MTH]),
      FILTER(ALLEXCEPT('vw_PandL','vw_PandL'[DateKey],'vw_PandL'[DimSiteKey]),
          [Line]="TOTAL REVENUE")
          )
  </code></pre>
  
  <p><strong>Update 1:</strong></p>
  
  <p>I managed to reproduce this in a simple model</p>
  
  <p>Among many things what I didn't mention is that the <code>LINE</code> column is sorted by another column called <code>SortZZ</code></p>
  
  <p>The calc posed in @Foxan Ng answer works perfectly but as soon as I sort the Line column, it exhibits the behaviour I mentioned: the Total Revenue figure only appears on the Total Revenue line</p>
  
  <p>After further investigation: I don't even need to sort it, I can just put the SortZZ column in the list and it will break the calc.</p>
  
  <p><strong>Solution</strong></p>
  
  <p>The final functioning calc was:</p>
  
  <pre><code>Total Revenue = 
  CALCULATE(
      SUM([MTH]),
      FILTER(
          ALLEXCEPT('vw_PandL','vw_DimPeriod'[Period],'DimSite'[Site]),
          'vw_PandL'[Line] = "Total Revenue")
  )
  </code></pre>
  
  <p>The trick is in <code>ALLEXCEPT</code></p>
  
  <p>My relationships and slicers are set up as follows:</p>
  
  <ul>
  <li><code>vw_PandL</code> is a fact table</li>
  <li><code>vw_DimPeriod</code> is joined to it on <code>PeriodCode</code></li>
  <li><code>DimSite</code> is also joined to it on <code>SiteKey</code></li>
  </ul>
  
  <p>When using any of the following <code>ALLEXCEPT</code> expressions, the calculation did not honour my slicers. That is, the figure was the grand total for the line across all periods and sites:</p>
  
  <ol>
  <li><p>Referring to join keys in fact table:</p>
  
  <p><code>ALLEXCEPT('vw_PandL','vw_PandL'[PeriodCode],'vw_PandL'[SiteKey])</code>   </p></li>
  <li><p>Referring to join keys in dimensions:</p>
  
  <p><code>ALLEXCEPT('vw_PandL','vw_DimPeriod'[PeriodCode],'DimSite'[SiteKey])</code></p></li>
  </ol>
  
  <p>The only way I could get the calc to honour my slicers was to use the actual field that I was physically using in my slicer, in other words the label</p>
  
  <pre><code>ALLEXCEPT('vw_PandL','vw_DimPeriod'[Period],'DimSite'[SiteName])
  </code></pre>
  
  <p>The key thing to learn from this is this is not just a relational database where you join on a key then filtering on the label will work. The columns and tables mentioned in these functions are very important.</p>