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/12/24.     Closed.  £34.21

123.            13/12/24.    Closed.  £12.89

123.            13/12/24.    Closed.  £45.35

456.          12/12/24.      Closed.  £10.50

456.          13/12/24.      Closed.  £20.50

456.          14/12/24.       Open.   £30.00

 

The question they are asking is that for every customer who has a status of Open, how much have they spent in total, os in this instance my table visual will look like this.

 

CustID.         Total spend with an open status

456.               £61

 

I'm.not certain it's possible but wanted to see if anyone is cleverer than me!

 

Many thanks.

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

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    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])
    )


     

     

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi Daretoexplore ,

    Yes, it’s definitely possible to calculate the total spend for customers with an “Open” status in Power BI. 

     

    Step 1- Create a new measure using bellow DAX:

     

     

    Total Spend with Open Status = 
CALCULATE(
    SUM(Transactions[Amount]),
    FILTER(
        Transactions,
        Transactions[CustID] IN 
            SELECTCOLUMNS(
                FILTER(
                    Transactions,
                    Transactions[Status] = "Open"
                ),
                "CustID", Transactions[CustID]
            )
    )
)

     

     

     

    Step 2- Create a Table Visual and Add CustID and the new measure TotalSpendOpen to your table visual.

    This will give you a table where you can see the total spend for each customer with an “Open” status.

     

     

     

  • 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

  • BITomS's avatar
    BITomS
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi Daretoexplore ,

     

    Measure = IF ( CONTAINS , 'TableName' , [Status] , "Open.") , SUM ( [Amount] ) , BLANK() )