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
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.