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
I have below sample reports budget and actual by mth and requrie to output report by quarter.
Appreciate any advice. Thanks.
Solved! Go to Solution.
Hi @Grace1 ,
Please check if this could meet your requirement:
1. Connect to Excel file.
In Power Query Editor,
2. Transform data of both Actual and Budget tables:
3. Merge queries as new.
4. Expand table.
5. Add conditional column.
6. Filter rows.
7. Remove column "Month.1" and "Month eq Month.1".
8. Rename column "Budget.1" with "Budget".
9. Add conditional column.
10. Change type of "MonthNum" column.
11. Close and Apply.
In Power BI Desktop,
12. Create a Calendar table.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Quarter", QUARTER ( [Date] )
)
13. Create relationship.
14. Create columns in Calendar table.
Q = SWITCH ( 'Calendar'[Quarter], 1, "1Q", 2, "2Q", 3, "3Q", 4, "4Q" )
Half Year = SWITCH('Calendar'[Quarter],1,"1H",2,"1H",3,"2H",4,"2H")
15. Create a Measure.
Banlance = SUM('Actual & Budget'[Actual])-SUM('Actual & Budget'[Budget])
16. Create a Matrix visual.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Grace1 ,
Try to create a [MonthNum] column and then sort your [Month] column by it.
Reference:
Sort By Month Names In Power BI;
Power BI Tips: Sort by Month Name.
Best Regards,
Icey
Hi @Grace1 ,
Please check if this could meet your requirement:
1. Connect to Excel file.
In Power Query Editor,
2. Transform data of both Actual and Budget tables:
3. Merge queries as new.
4. Expand table.
5. Add conditional column.
6. Filter rows.
7. Remove column "Month.1" and "Month eq Month.1".
8. Rename column "Budget.1" with "Budget".
9. Add conditional column.
10. Change type of "MonthNum" column.
11. Close and Apply.
In Power BI Desktop,
12. Create a Calendar table.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Quarter", QUARTER ( [Date] )
)
13. Create relationship.
14. Create columns in Calendar table.
Q = SWITCH ( 'Calendar'[Quarter], 1, "1Q", 2, "2Q", 3, "3Q", 4, "4Q" )
Half Year = SWITCH('Calendar'[Quarter],1,"1H",2,"1H",3,"2H",4,"2H")
15. Create a Measure.
Banlance = SUM('Actual & Budget'[Actual])-SUM('Actual & Budget'[Budget])
16. Create a Matrix visual.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey
Can i have the source file : Budget vs Actual.xlsx ?
Thanks
Hi @Grace1 ,
The data is from your screenshots. Please check the attached .xlsx file.
Best Regards,
Icey
Thanks.
Hi Icey
I have try and it works from the data from my screen shot.
But when I try for another type of expense, the Month appear in alphabetic order both from Budget and Actual.
(ie. Apr, Aug...etc)
But I need to appear as Apr, May, Jun....etc
Attach are 2 pic for your viewing.
Thanks for your help.
Hi @Grace1 ,
Try to create a [MonthNum] column and then sort your [Month] column by it.
Reference:
Sort By Month Names In Power BI;
Power BI Tips: Sort by Month Name.
Best Regards,
Icey
Dear Icey
I don't know why different asset codes have the same budget and actual values. Should have different values.
Appreciate advice.
Thank & Regard
Grace
Dear Icey
Please ignore the above issue. I found the cause of the error.
Thank & Regard
Grace
Got it, thank you Icey for your quick feedback.
Appreciate your help.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Refer
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://radacad.com/pivot-and-unpivot-with-power-bi
Appreciate your Kudos.
Hi,
By balance do you mean variance? Try this
Total = SUM(Data[Amount])
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 |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |