Forum Discussion

Daretoexplore's avatar
Daretoexplore
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Active people

Hey all   My client has given me something that I'm not sure is possible, but wanted to check.   My data is something like this.   CustID.      TransDate.    Status.   Amount 123.           12...
Need Help
  • Visharavana's avatar
    Visharavana
    1 year ago

    Hi Daretoexplore 

    Total Spend with Open Status = 
VAR OpenCustomers = 
    FILTER(
        SUMMARIZE(
            Data,
            Data[CustID],
            "HasOpen", 
            CALCULATE(MAXX(Data, IF(Data[Status] = "Open", 1, 0)))
        ),
        [HasOpen] = 1
    )
RETURN 
    CALCULATE(
        SUM(Data[Amount]),
        TREATAS(SELECTCOLUMNS(OpenCustomers, "CustID", Data[CustID]), Data[CustID])
    )


     

     

  • johnt75's avatar
    johnt75
    1 year ago

    Try

    Total for open customers =
VAR OpenCustomers =
    CALCULATETABLE ( VALUES ( 'Table'[Cust ID] ), 'Table'[Status] = "Open" )
VAR Result =
    SUMX ( OpenCustomers, CALCULATE ( SUM ( 'Table'[Amount] ) ) )
RETURN
    Result
rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi Daretoexplore - Yes, it is absolutely possible to calculate this

Create a Measure for "Total Spend with Open Status"

Total Spend with Open Status =
CALCULATE(
SUM('YourTable'[Amount]),
FILTER(
'YourTable',
'YourTable'[CustID] IN
CALCULATETABLE(
VALUES('YourTable'[CustID]),
'YourTable'[Status] = "Open"
)
)
)

 

This works, please check and confirm