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
Visharavana's avatar
Visharavana
Icon for Resolver II rankResolver II
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])
    )


 

 

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

This is an amazing solution, thank you!