Forum Discussion

ldgary's avatar
ldgary
New Member
1 year ago
Solved

Getting a table total into a measure

I have a Power BI report that has some complicated measures, most of which I've figured out. One that I can't seem to get is a measure that shows the number of transactions for those sales within a specified range. This is what I have now with incorrect Transaction Counts:

 

This table shows the data that comprises of what I need to see in the Transaction Count:

 

The highlighted total is the amount I need. Here is the DAX for the TY Invoice Count measure:

 

Here is my Transaction Count measure that isn't giving me what I need:

 

All of the other amounts are directly from the semantic model that is used for the report. Are there any ideas of how I can accomplish this?

Need Help
  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    After reviewing the screenshot again, it looks like we first need to aggregate the sales by SellToNo. The previous measure was aggreageting by the Invoice. Please try this one instead:

    Transaction Count > $100,000 =
SUMX(
    FILTER(
        VALUES(Fact_Invoice[SellToNo]),
        CALCULATE(SUM(Fact_Invoice[NetSales])) > 100000
    ),
    [TY Invoice Count] 
)

     

7 Replies

  • SamsonTruong's avatar
    SamsonTruong
    Super User
    1 year ago

    Hi ldgary ,

     

    Please try the following adjusted DAX for your transaction count measure and let me know if it achieves the desired result:

    Transaction Count > $100,000 = 
VAR InvoicesOver100K =
    FILTER(
        SUMMARIZE(
            Fact_Invoice,
            Fact_Invoice[InvoiceNo],
            "TotalSales", SUM(Fact_Invoice[NetSales])
        ),
        [TotalSales] > 100000
    )
RETURN
    COUNTROWS(
        FILTER(
            InvoicesOver100K,
            CALCULATE(
                SELECTEDVALUE(Dim_TransActSalesPerson[SalesPersonName])
            ) = SELECTEDVALUE(Dim_TransActSalesPerson[SalesPersonName])
        )
    )

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson



      • SamsonTruong's avatar
        SamsonTruong
        Super User
        1 year ago

        Hi ldgary ,

        Can we try the following without the sales person reference to see if this gets us closer:

        Transaction Count > $100,000 =
CALCULATE(
    COUNTROWS(
        FILTER(
            SUMMARIZE(
                Fact_Invoice,
                Fact_Invoice[InvoiceNo],
                "TotalSales", SUM(Fact_Invoice[NetSales])
            ),
            [TotalSales] > 100000
        )
    )
)