Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
)
)
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 )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |