How to write Tableau formula in Power BI AVG(fixed:sum with if-function)

[nikulbhuva]

Hi,

I could like to write the following Tableau formula in Power BI:

avg({fixed [Unique Transaction ID]: sum(iif([Department]='Shop',[SalesValueInclGST],0))})

I've been struggling for 2 days and I can't find any solution. I've tried the following, but I get incorrect numbers:

 DAX QUERY AS BELOW

DEFINE
    MEASURE Transactions[SalesValueCustom] =
        CALCULATE (
            DIVIDE (
                SUMX (
                    Transactions,
                    IF (
                        FIRSTNONBLANK ( Products[DepartmentCode], "00" ) = "02",
                        Transactions[_SalesValue]
                    )
                ),
                [Distinct Count of UniqueTransactionID]
            ),
            ALLEXCEPT ( Transactions, Transactions[UniqueTransactionID] )
        )

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Transactions',
            Products[ProductDescription]
        ),
        "SalesValue", [SalesValue],       
        "TranCount", [Distinct Count of UniqueTransactionID],
        "SalesValueTS", [SalesValueCustom],
    ),
    FILTER ( Products, Products[CategoryCode] = "XXX" )
)

Thanks in Advance. 

Please help me as soon as possible, really badly stuck with the above issue. 

[nikulbhuva · Accepted solution]

Hi Guys, I got an answer and this is now has been resolved. Thanks. 

Measure = 
  AVERAGEX( (
            SUMMARIZE ( Transactions, Transactions[UniqueTransactionID] ),
            CALCULATE (
                SUMX (
                     Transactions,
                  IF (
                        FIRSTNONBLANK (Products[DepartmentCode],"") = "02",
                        Transactions[_SalesValue]                    
                    )
                ),
                ALL ( Products )
            )
        )