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

     

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



ldgary's avatar
ldgary
New Member
1 year ago

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

 

 

  • 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
        )
    )
)
    • ldgary's avatar
      ldgary
      New Member
      1 year ago

      It's still blank.

      • SamsonTruong's avatar
        SamsonTruong
        Super User
        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] 
)