Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
8 years ago

Summing intermediate levels in a matrix using ALL or ALLEXCEPT

I'm sure thisis realyl easy, but just cant work it out. I have amatrix that looks like this

 

First Row is Centre[Depot Abbrev]

2nd row is Header[Document No]

3rd Row is Lines[Description]

 

There is a relationship between Centre and Header basedon centre no and between Header and lines based on Document No. Each Dcoument has multiple lines (these are invoices).

 

Each line of the lines table has a gross sale calculation so Total Gross Sales=SUM(Lines[Gross Sales])

 

I need to create a measure where Total Gross sale is the total of the document on every line instead of the gross sale of that line.  So in this case the measure would have £33.29 on both rows, but the total for AND woudl stil lbe the toal of all documents at AND.  I have tried all sorts of combinations and ALL and ALLEXCEPT, but can't work it out.  

 

Apprecaite any help

Mike

 

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi masplin,

     

    Please try this measure:

    Total Gross Sales =
CALCULATE ( SUM ( Lines[Gross Sales] ), ALL ( Lines[Description] ) )

    If it doesn't meet your requirement, please post sample data of above three tables, and show us your desired result.

     

    Best regards,

    Yuliana Gu

    • masplin's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      8 years ago

      Hi

       

      Well that was the first thing I tried, but while it produces the right answer it also duplicates every single description in the Lines table for every invoice as below.   I can get round this doing this to hide the lines that shoudl be there, but seems inelegant.

       

      GP% Invoice = IF([Total Gross Sales]<>BLANK(),
                             CALCULATE(SUM('Posted Document Line'[Gross Sale]),
                                                   ALL('Posted Document Line'[Description])
                                                    ) ,
                               BLANK()
                              )