Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I have to build a matrix with a Product column and a Budget column, however each product needs to show the monthly, quarterly, annual budget. Here's a visual of what I'm trying to accomplish:
Budget | ||
Product A | Fiscal Month | 2282 |
Fiscal Quarter | 2734 | |
Fiscal Year | 3226 | |
Product B | Fiscal Month | 2402 |
Fiscal Quarter | 2878 | |
Fiscal Year | 3396 | |
Product C | Fiscal Month | 3226 |
Fiscal Quarter | 4532 | |
Fiscal Year | 5865 |
The Budget value comes from a measure:
Solved! Go to Solution.
Hi @RadMich ,
Add a row “Total” to the Products column of the Products table.
And the measure is as follows.
Budget =
VAR result=
SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
RETURN
IF(SELECTEDVALUE('Product'[Product])<>"Total",result,
SWITCH(
SELECTEDVALUE(Period[Period]),
"Fiscal Month", CALCULATE([MonthlyBudget],ALL('Product'[Product])) ,
"Fiscal Quarter",CALCULATE([QuarterlyBudget],ALL('Product'[Product])),
"Fiscal Year", CALCULATE([AnnualBudget],ALL('Product'[Product]))
)
)
The final result is as follows. Hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply from VahidDM, please allow me to add some more suggestions.
Hi @RadMich
You only need to make the following changes in the measure provided by the VahidDM.
Budget =
VAR result=
SWITCH(SELECTEDVALUE(Period[Period]),"Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
RETURN
SWITCH(
TRUE(),
ISFILTERED(
'Product'[Product])=FALSE(),CALCULATE([mBudget],ALL('Period'[Period]),ALL('Product'[Product])),
ISFILTERED('Period'[Period])=FALSE(),CALCULATE([mBudget],ALL('Period'[Period])),
result)
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Dengliang Li,
thank you so much for your input, but it does not provide the solution I need. I'm looking for a total by Fiscal Month, Fiscal Quarter and Fiscal Year, as follows. Could you please help to achieve this result? Thank you
Product A | Fiscal Month | 2282 |
Fiscal Quarter | 2784 | |
Fiscal Year | 3226 | |
Product B | Fiscal Month | 2402 |
Fiscal Quarter | 2878 | |
Fiscal Year | 3396 | |
Product C | Fiscal Month | 3226 |
Fiscal Quarter | 4532 | |
Fiscal Year | 5865 | |
Total | Fiscal Month | 7910 |
Fiscal Quarter | 10194 | |
Fiscal Year | 12487 |
Hi @RadMich ,
Add a row “Total” to the Products column of the Products table.
And the measure is as follows.
Budget =
VAR result=
SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
RETURN
IF(SELECTEDVALUE('Product'[Product])<>"Total",result,
SWITCH(
SELECTEDVALUE(Period[Period]),
"Fiscal Month", CALCULATE([MonthlyBudget],ALL('Product'[Product])) ,
"Fiscal Quarter",CALCULATE([QuarterlyBudget],ALL('Product'[Product])),
"Fiscal Year", CALCULATE([AnnualBudget],ALL('Product'[Product]))
)
)
The final result is as follows. Hopefully it will meet your needs.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-denglli-msft, Thank you so much, I appreciate your help.
Hi @RadMich
You can achieve this by creating a custom "Period" table and using measures to calculate the budget for each period. Here's how:
Create a new table named Period with the following DAX:
Period = DATATABLE( "Period", STRING, { {"Fiscal Month"}, {"Fiscal Quarter"}, {"Fiscal Year"} } )
MonthlyBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Month] = SELECTEDVALUE('Date'[Fiscal Month]), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )
QuarterlyBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Quarter] = SELECTEDVALUE('Date'[Fiscal Quarter]), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )
AnnualBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )
BudgetMeasure = SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
Notes:
By setting up your data this way, your matrix will display each product with monthly, quarterly, and annual budget values that update based on the selected fiscal month.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hello VahidDM,
thank you so much, this is exactly what I needed.
I'm trying to show a total for all products by Fiscal Month, Fiscal Quarter, Fiscal Year by activating Row Subtotals,, but it's not showing any value. Would you be able to help with that as well? Thank you
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |