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.
PROBLEM: Some of the measure columns that have the exact same formula as others are returning blank (#3) or incorrect (#4) values, and I cannot figure out why.
SCENARIO: Here are the 3 tables (formatted to look like 1) that I've created along with numbered identifiers to explain them:
Since the client wants to see data in a this week over last week format (#1), I created a table of calculated values called Weekly Sales which draws on the custom query for 'Sales Transactions'[ThisWeek] and 'Sales Transactions'[LastWeek]:
Weekly Sales = SUMMARIZE(
FILTER('Sales Transactions', OR('Sales Transactions'[ThisWeek] = "1",'Sales Transactions'[LastWeek] = "1")),
'Sales Transactions'[ThisWeek],'Sales Transactions'[LastWeek],'Sales Transactions'[WeekdayNum],'Sales Transactions'[StoreId],
"Monday",IF('Sales Transactions'[WeekdayNum]=1,SUM('Sales Transactions'[Item Total Paid]),0),
"MondayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 1,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"MondayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 1,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Tuesday",IF('Sales Transactions'[WeekdayNum]=2,SUM('Sales Transactions'[Item Total Paid]),0),
"TuesdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 2,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"TuesdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 2,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Wednesday",IF('Sales Transactions'[WeekdayNum]=3,SUM('Sales Transactions'[Item Total Paid]),0),
"WednesdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 3,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"WednesdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 3,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Thursday",IF('Sales Transactions'[WeekdayNum]=4,SUM('Sales Transactions'[Item Total Paid]),0),
"ThursdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 4,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"ThursdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 4,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Friday",IF('Sales Transactions'[WeekdayNum]=5,SUM('Sales Transactions'[Item Total Paid]),0),
"FridayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 5,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"FridayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 5,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Saturday",IF('Sales Transactions'[WeekdayNum]=6,SUM('Sales Transactions'[Item Total Paid]),0),
"SaturdayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 6,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"SaturdayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 6,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"Sunday",IF('Sales Transactions'[WeekdayNum]=7,SUM('Sales Transactions'[Item Total Paid]),0),
"SundayTW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 7,'Sales Transactions'[ThisWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"SundayLW",IF(AND(VALUE('Sales Transactions'[WeekdayNum]) = 7,'Sales Transactions'[LastWeek] = "1"),SUM('Sales Transactions'[Item Total Paid]),0),
"WTD",SUM('Sales Transactions'[Item Total Paid]),
"WTDLW",IF('Sales Transactions'[LastWeek] = "1",SUM('Sales Transactions'[Item Total Paid]),0),
"WTDTW",IF('Sales Transactions'[ThisWeek] = "1",SUM('Sales Transactions'[Item Total Paid]),0),
"Description",IF('Sales Transactions'[ThisWeek],"Sales This Week","Sales Last Week")
)
And within that table, there are a number of measures for each day of the week plus WTD (#2) similar to:
SaturdayDiff = CALCULATE(SUMX('Weekly Sales','Weekly Sales'[SaturdayTW]-'Weekly Sales'[SaturdayLW]))
And more measures to calculate the diff in a % format similar to:
SatDiffPct = CALCULATE(DIVIDE(SUMX('Weekly Sales','Weekly Sales'[SaturdayTW] - 'Weekly Sales'[SaturdayLW]),SUMX('Weekly Sales','Weekly Sales'[Saturday])))
Hi @finzilla,
Does problem persist if testing with different data still using above measures?
Regards,
Yuliana Gu
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |