Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I’m currently building a forecast model in Power BI that relies on multiple fact tables. The model is designed to display the entire year 2024, broken down by individual months (Jan-Dec). I’m using four different tables for this:
The 2024 timeline should be filled with the revenue from Monthending for all months as long as this dataset contains data. The following month after the last dataset should determine the cumulative value of Orders & Invoices for the current month. The subsequent months should be filled with the Prod-FC.
I’ve divided this calculation into two measures:
Rev-AC 4 =
VAR CurrentPeriod = MONTH(MAX('invoices'[Invoice DATE]))
VAR Customer = SELECTEDVALUE('CustomerGroups'[CustomerSTAT])
VAR OrdersREV = CALCULATE(SUM('orders'[Amount]), ALL('Orders'), 'Orders'[OMonth] <= CurrentPeriod, 'orders'[CustomerSTAT] = Customer)
VAR InvoicesREV = CALCULATE(SUM('invoices'[Amount]), ALL('Invoices'), 'invoices'[IMonth] = CurrentPeriod, 'invoices'[CustomerSTAT] = Customer)
VAR TotalOrderREV = CALCULATE(SUM('orders'[Amount]), ALL('Orders'), 'Orders'[OMonth] <= CurrentPeriod)
VAR TotalInvoicesREV = CALCULATE(SUM('invoices'[Amount]), ALL('Invoices'), 'invoices'[IMonth] = CurrentPeriod)
RETURN
IF(CurrentPeriod = 3, IF(HASONEVALUE('CustomerGroups'[CustomerSTAT]), OrdersREV + InvoicesREV, TotalOrderREV + TotalInvoicesREV), BLANK())
FC_REV 3 =
VAR ME_REV = CALCULATE(SUM('monthending'[Amount]))
VAR RevAC = [Rev-AC 4]
VAR ProdFc_REV = CALCULATE(SUM('prod-fc'[Amount]))
RETURN
IF(
NOT(ISBLANK(RevAC)), RevAC,
IF(
NOT(ISBLANK(ME_REV)), ME_REV,
IF(
NOT(ISBLANK(ProdFc_REV)), ProdFc_REV,
BLANK()
)
)
)
I’m encountering a problem with the second measure (maybe 1st measure also). While the distribution to the individual months in this model works without issues (although it’s not dynamically determined and requires a clear definition of the current month by me), and the column subtotals are also correct, the row total is incorrect. In the row total, the result of the current month is displayed instead of the sum of all individual sums of the months.
Perhaps my DAX approach is too complicated, but I hope someone can shed some light on this issue. Attached you will find the data basis and the PBIX file.
https://drive.google.com/drive/folders/1v16In4_XjfpwSmhnPqd_mKokiDAO-NWB?usp=drive_link
Thank you in advance for your help!
Solved! Go to Solution.
Hi @T_JF2022
This code should work.
Gobal2 =
VAR MaxDateActuals =
CALCULATE ( MAX ( monthending[Finished Period] ), REMOVEFILTERS () )
VAR moisAct =
MONTH ( MaxDateActuals )
VAR act =
SUMMARIZE (
monthending,
monthending[Amount],
monthending[CustomerSTAT],
monthending[Finished Period],
"Origin", "Actuals"
)
VAR Inv =
SUMMARIZE (
invoices,
invoices[Amount],
invoices[CustomerSTAT],
invoices[Invoice DATE],
"Origin", "Invoice"
)
VAR Ord =
SUMMARIZE (
orders,
orders[Amount],
orders[CustomerSTAT],
orders[Planned DATE],
"Origin", "Invoice"
)
VAR Frcst =
SUMMARIZE (
'prod-fc',
'prod-fc'[Amount],
'prod-fc'[CustomerSTAT],
'prod-fc'[Production Period],
"Origin", "Forecast"
)
VAR base1 =
UNION ( act, Inv )
VAR base2 =
UNION ( Ord, Frcst )
VAR Endingbase =
FILTER (
ADDCOLUMNS (
UNION ( base1, base2 ),
"MoisFinal",
IF (
AND ( MONTH ( monthending[Finished Period] ) <= moisAct, [Origin] = "Actuals" ),
MONTH ( monthending[Finished Period] ),
IF (
AND (
MONTH ( monthending[Finished Period] ) <= moisAct + 1,
[Origin] = "Invoice"
),
moisAct + 1,
IF (
AND (
MONTH ( monthending[Finished Period] ) >= moisAct + 2,
[Origin] = "Forecast"
),
MONTH ( monthending[Finished Period] ),
0
)
)
)
),
[MoisFinal] <> 0
)
VAR res2 =
SUMX ( Endingbase, [Amount] )
RETURN
res2
Hi @T_JF2022
This code should work.
Gobal2 =
VAR MaxDateActuals =
CALCULATE ( MAX ( monthending[Finished Period] ), REMOVEFILTERS () )
VAR moisAct =
MONTH ( MaxDateActuals )
VAR act =
SUMMARIZE (
monthending,
monthending[Amount],
monthending[CustomerSTAT],
monthending[Finished Period],
"Origin", "Actuals"
)
VAR Inv =
SUMMARIZE (
invoices,
invoices[Amount],
invoices[CustomerSTAT],
invoices[Invoice DATE],
"Origin", "Invoice"
)
VAR Ord =
SUMMARIZE (
orders,
orders[Amount],
orders[CustomerSTAT],
orders[Planned DATE],
"Origin", "Invoice"
)
VAR Frcst =
SUMMARIZE (
'prod-fc',
'prod-fc'[Amount],
'prod-fc'[CustomerSTAT],
'prod-fc'[Production Period],
"Origin", "Forecast"
)
VAR base1 =
UNION ( act, Inv )
VAR base2 =
UNION ( Ord, Frcst )
VAR Endingbase =
FILTER (
ADDCOLUMNS (
UNION ( base1, base2 ),
"MoisFinal",
IF (
AND ( MONTH ( monthending[Finished Period] ) <= moisAct, [Origin] = "Actuals" ),
MONTH ( monthending[Finished Period] ),
IF (
AND (
MONTH ( monthending[Finished Period] ) <= moisAct + 1,
[Origin] = "Invoice"
),
moisAct + 1,
IF (
AND (
MONTH ( monthending[Finished Period] ) >= moisAct + 2,
[Origin] = "Forecast"
),
MONTH ( monthending[Finished Period] ),
0
)
)
)
),
[MoisFinal] <> 0
)
VAR res2 =
SUMX ( Endingbase, [Amount] )
RETURN
res2
Insane, it worked 😋
Hi @T_JF2022
In Power BI, if the row totals for a matrix are incorrect, this is usually because of the way the metric is calculated.
To ensure that the row totals are displayed correctly, you can try the following steps:
Ensure that your metrics are calculated correctly in the detail rows.
I notice that you are using sum for your summation, perhaps you could consider using sumx.
Use the SUMX function to wrap your metric value on top of your metric value to make sure that both the detail rows and the total rows are correct.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |