RE: Unique sum

[Anonymous]

Hi. Team. I need your help on this. I have to make a report before the end of the month. 

From this table:

To this outcome:

Hope this can help. TIA 

[DataInsights]

@Anonymous,

Here's a solution based on my understanding of the requirements:

1. Orig Amount in the table visual is the sum of Orig Amount where Doc Code Sign = Y.

2. Audited Amt in the table visual is calculated as follows:

a. Count the number of rows for each Doc Code.

b. If count = 1, then sum Audited Amt.

c. If count > 1, then sum Audited Amt where Doc Code Sign <> Y.

Solution:

1. Create three measures. The second measure is used for calculation only; it is not displayed in the table visual.

Total Orig Amount = CALCULATE ( SUM ( Docs[Orig Amount] ), Docs[Doc Code Sign] = "Y" )

Audited Amount Calc = 
VAR vAuditAmt =
    SUM ( Docs[Audited Amt] )
VAR vCurDocCode =
    MAX ( Docs[Doc Code] )
VAR vCurDocCodeRows =
    FILTER ( ALL ( Docs ), Docs[Doc Code] = vCurDocCode )
VAR vCountDocCode =
    COUNTROWS ( vCurDocCodeRows )
VAR vRowsToSum =
    FILTER ( vCurDocCodeRows, Docs[Doc Code Sign] <> "Y" )
VAR vResult =
    IF ( vCountDocCode = 1, vAuditAmt, SUMX ( vRowsToSum, Docs[Audited Amt] ) )
RETURN
    vResult

Total Audited Amount = 
--this measure is necessary in order to properly calculate totals
VAR vDocCodeTable =
    ADDCOLUMNS (
        SUMMARIZE ( Docs, Docs[Doc Code] ),
        "AuditedAmountCalc", [Audited Amount Calc]
    )
VAR vResult =
    SUMX ( vDocCodeTable, [AuditedAmountCalc] )
RETURN
    vResult

2. Create a table visual and filter Doc Code Sign = Y.