Getting a table total into a measure

[ldgary]

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?

[SamsonTruong]

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

[ldgary]

Thanks, @SamsonTruong, but this didn't work for me.

[SamsonTruong]

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

[ldgary]

It's still blank.

[SamsonTruong · Accepted solution]

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] 
)

[ldgary]

That was it, and it was so simple, thank you!

[SamsonTruong]

Awesome, glad to hear it worked!