Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, ...
Patrick95's avatar
Patrick95
Frequent Visitor
4 years ago

Hi,

 

I've created a measure to calculate the distinct "Document No." when they have a certain value (FILTER ON: '42-CHECK-SERIALNO'). 

It could be that the same Document No. will have the value (42-...) on a different date. 

 

The totals are corrrect in the matrix, but the subtotals will count the value even if the Document No. has already hit the value (42-CHECK-SERIALNO) already on a different date. Herefor if you count the subtotals, you will get a different total als the one that is listed correctly in the matrix.

 

If the "Document No." occurs several times on different dates by value (42-...), then I do not want to count this (double) in the subtotals. It should only count the first time the "Document No." hits the status (42-...)

 

 

 

 

 

I hope someone can help me with this issue

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    What is the problem there.  The subtotal shows 34.  Are you expecting a different result there?

    • Patrick95's avatar
      Patrick95
      Frequent Visitor
      4 years ago

      Hi Ashish,

       

      Thank you for replying! 

       

      As you can see, the total is 34 which is correct. If you add up the subtotals (10+17+12+9 = 48), you get a different result. The addition of the subtotals is incorrect.  The subtotals will count the value even if the same Document No. has the value (42-CHECK-SERIALNO) on a different date.

       

      Therefor, I would like to create a measure: If the "Document No." occurs several times on different dates by value (42-...), then I do not want to count this (double) in the subtotals. It should only count the first time the "Document No." hits the status (42-...).

       

      Unfortunately, I cannot figure out how to do this.

       

      Do you have experience how to do this?

       

      Thanks in advance.

       

      Patrick

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Share the link from where i can download your PBI file.