Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
I have found my most bizzare case in Power BI yet and would love some help. My measure is returning correctly for each row, but fails when calculating the column totals. For sake of debugging my dax, I have replaced my intented measure with a value of 1.
What I am seeing is that the column totals are only calculating when there is data in the 'receipts' table. Jan has a one receipt as is returning 1, June has receipts for both keys and is returning two. July to Dec have no receipts and are returning blank.
The expected outcome will correctly sum the column totals (all would be 2 in this example).
My model is very simple. It is comprised of a the 'Receipts' table and a fairly standard date table.
Receipts sample:
Eff Date | Extended Cost | Quantity | Site_Item_Key |
3/13/2023 | 9.7 | 100 | SFA250 |
3/31/2023 | 9.7 | 100 | SFA250 |
6/26/2023 | 9.327 | 100 | SFA250 |
1/12/2023 | 325.56 | 6 | SFA479 |
4/3/2023 | 927.1 | 10 | SFA479 |
5/11/2023 | 927.1 | 10 | SFA479 |
6/22/2023 | 8045.9 | 305 | SFA479 |
Main sections would be the vTable and column totals
Monthly Inflation (TEST3) =
VAR SelMo = SELECTEDVALUE( 'Date'[Year Month] )
VAR SelKey = SELECTEDVALUE( Receipts[Site_Item_Key] )
VAR vTable =
ADDCOLUMNS(
SUMMARIZE(
GENERATE(
VALUES(Receipts[Site_Item_Key]),
VALUES('Date'[Year Month])
),
Receipts[Site_Item_Key],
'Date'[Year Month]
),
"@Monthly Inflation", 1 -- PLACEHOLDER/Test
)
Var Result =
SWITCH(
TRUE(),
ISINSCOPE( Receipts[Site_Item_Key] )
&& ISINSCOPE( 'Date'[Year Month] ), 1, // BASE DATA ROWS
ISINSCOPE( 'Date'[Year Month] ), // COLUMN TOTALS - HELP
CALCULATE(
SUMX(
vTable,
1
),
VALUES( Receipts[Site_Item_Key] )
),
ISINSCOPE( Receipts[Site_Item_Key] ), // ROW TOTALS
CALCULATE(
SUMX(
vTable,
[@Monthly Inflation]
),
VALUES( 'Date'[Year Month] )
), // GRAND TOTAL
SUMX(
vTable,
[@Monthly Inflation]
)
)
RETURN
Result
The "1" place holder will be replaced with a measure [Monthly Inflation (R)]
Monthly Inflation (Test4) =
VAR SelMo = SELECTEDVALUE('Date'[Year Month])
VAR SelKey = SELECTEDVALUE(Receipts[Site_Item_Key])
VAR vTable =
ADDCOLUMNS(
SUMMARIZE(
GENERATE(
VALUES(Receipts[Site_Item_Key]),
VALUES('Date'[Year Month])
),
Receipts[Site_Item_Key],
'Date'[Year Month]
),
"@Monthly Inflation", 1 // Placeholder
)
VAR RowTotal =
CALCULATE(
SUMX(
vTable,
[@Monthly Inflation]
),
ALL('Date'[Year Month])
)
VAR ColumnTotal = // HELP
CALCULATE(
SUMX(
vTable,
[@Monthly Inflation] ),
ALL(Receipts[Site_Item_Key]),
VALUES('Date'[Year Month])
)
VAR GrandTotal =
SUMX(
vTable,
[@Monthly Inflation]
)
RETURN
IF(
ISINSCOPE(Receipts[Site_Item_Key]) && ISINSCOPE('Date'[Year Month]),
1, // Placeholder
IF(
ISINSCOPE('Date'[Year Month]),
ColumnTotal,
IF(
ISINSCOPE(Receipts[Site_Item_Key]),
RowTotal,
GrandTotal
)
)
)
Newest Revision
Hi,
Your expected result is not clear. For a sample dataset, show the expected result.
Hi, thank you for taking time to review. I just updated the post so that the picture used the same data as the sample and added more detail.
Please let me know if there is any more clarify I can provide. Thanks again!
Hi,
Share the download link of the PBI file.
I am unfortunally not able to at this time. But the model is extremely simple.
There is only two tables and both are included below.
Receipts:
Eff Date | Quantity | Site_Item_Key |
3/13/2023 | 100 | SFA250 |
3/31/2023 | 100 | SFA250 |
6/26/2023 | 100 | SFA250 |
1/12/2023 | 6 | SFA479 |
4/3/2023 | 10 | SFA479 |
5/11/2023 | 10 | SFA479 |
6/22/2023 | 305 | SFA479 |
Date:
Date =
ADDCOLUMNS (
CALENDAR ( DATE(2023,1,1), DATE(2023,12,31) ),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"Month No", MONTH ( [Date] ),
"Quarter", "Q" & CEILING ( MONTH ( [Date] ) / 3, 1 ),
"Week No", WEEKNUM ( [Date] ),
"Weekday", FORMAT ( [Date], "dddd" ),
"Weekday No", WEEKDAY ( [Date], 2 ) + 1,
"Year Month", FORMAT ( [Date], "MMM yyyy" ),
"Month Year No", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"Week Year", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
"Month Sort", YEAR ( [Date] ) * 10000 + MONTH ( [Date] ),
"Week Sort", YEAR ( [Date] ) * 10000 + WEEKNUM ( [Date] )
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
74 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |