Forum Discussion
Dynamic conditional formatting for gauge tile
Hi NikkiSavage
(1) "What I want is for the gauge to change colour from red to orange once 80% of budget has been reached and then green once 100% has been reached"
create a measure [Color], then put it into the Format pane of that visual, here is a related question, link.
(2) "I also want the gauge maximum to change to be the higher of either budget consulting revenue or actual consulting revenue. "
Create a measure [gauge maximum], in this measure create two variables to get budget consulting revenue and actual consulting revenue, then compare the two variables and return the maximum value
If you need more help, please provide more details,
(1) a sample file, you can replace raw data with bogus data to protect your privacy.
(2) or provide some sample data that fully covers your issue/question
(3) give your expected result based on the sample you provide
Kindly note: Please ensure the data in sample is concise and representative.
Thanks.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
let
Source = Excel.Workbook(File.Contents("C:\Users\61431\Desktop\GCC\Green Cloud Consulting Pty Ltd - Profit Loss All Time.xls"), null, true),
#"Transposed Data1" = Source{[Name="Transposed Data AU & PNG"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Data1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", Int64.Type}, {"Consulting Revenue", type number}, {"Cost Recoveries", type number}, {"Interest Income", type number}, {"Other Revenue", type number}, {"Management Fee Income", type number}, {"Consulting Expenses", type number}, {"Contractors", type number}, {"Direct Annual Leave Expense", type number}, {"Direct Superannuation", type number}, {"Direct Wages and Salaries", type number}, {"Discounts Provided", type number}, {"Direct Wages and Salaries - Redundancy", type number}, {"Wage offset to align budget & actuals", type number}, {"Gain/(Loss) on Sale of Assets", type number}, {"JobKeeper", Int64.Type}, {"PAYG Cashboost ATO", Int64.Type}, {"Advertising", type number}, {"Amortisation", type number}, {"Bad Debt Expense", Int64.Type}, {"Annual Leave Expense", type number}, {"Bank Fees", type number}, {"Bookkeeping", type number}, {"Conferences", type number}, {"Consulting & Accounting", type number}, {"Depreciation", type number}, {"Dividend offset account to align actuals vs budget", type number}, {"Donations", Int64.Type}, {"Entertainment", type number}, {"Equipment Expense", type number}, {"Filing Fees", type number}, {"Foreign Currency Gains and Losses", type number}, {"Freight & Courier", type number}, {"FX Gain or Loss", type number}, {"General Expenses", type number}, {"Insurance", type number}, {"Interest Expense", type number}, {"Legal expenses", type number}, {"Light, Power, Heating", type number}, {"Marketing Expenses", type number}, {"Motor Vehicle Expenses", type number}, {"Motor Vehicle Fuel", type number}, {"Office Expenses", type number}, {"Outgoings", type number}, {"Printing & Stationery", type number}, {"Recruitment Costs", type number}, {"Registrations", Int64.Type}, {"Rent", type number}, {"Repairs and Maintenance", type number}, {"Subscriptions", type number}, {"Superannuation", type number}, {"Telephone & Internet", type number}, {"Training", type number}, {"Travel - International", Int64.Type}, {"Travel - National", type number}, {"Uniforms", type number}, {"Website Expenses", type number}, {"Dividend Paid", type number}, {"Income Tax Expense", type number}, {"Total Income", type number}, {"Cost of Sales", type number}, {"Gross Profit", type number}, {"Total Other Income", type number}, {"Total Operating Expenses", type number}, {"Operating Profit", type number}, {"Total Non-Operating Expenses", type number}, {"Ner Profit", type number}, {"Number of employees at EOM", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Ner Profit", "Net Profit"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Consulting Revenue", type number}, {"Cost Recoveries", type number}, {"Interest Income", type number}, {"Other Revenue", type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Date", "Date - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Date - Copy", "Month"}}),
#"Extracted Month Name" = Table.TransformColumns(#"Renamed Columns1", {{"Month", each Date.MonthName(_), type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month Name", "Date", "Date - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "Calendar Month"}}),
#"Extracted Month" = Table.TransformColumns(#"Renamed Columns2",{{"Calendar Month", Date.Month, Int64.Type}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Extracted Month", "Calendar Month", "Calendar Month - Copy"),
#"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column2",{{"Calendar Month - Copy", "Financial Period"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"Financial Period"}),
#"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Financial Period", each if [Calendar Month] = 7 then 1 else if [Calendar Month] = 8 then 2 else if [Calendar Month] = 9 then 3 else if [Calendar Month] = 10 then 4 else if [Calendar Month] = 11 then 5 else if [Calendar Month] = 12 then 6 else if [Calendar Month] = 1 then 7 else if [Calendar Month] = 2 then 8 else if [Calendar Month] = 3 then 9 else if [Calendar Month] = 4 then 10 else if [Calendar Month] = 5 then 11 else if [Calendar Month] = 6 then 12 else 0),
#"Renamed Columns4" = Table.RenameColumns(#"Added Conditional Column",{{"Calendar Month", "Calendar Month"}, {"Month", "Month"}, {"Financial Period", "Financial Period"}}),
#"Duplicated Column3" = Table.DuplicateColumn(#"Renamed Columns4", "Date", "Date - Copy"),
#"Extracted Year" = Table.TransformColumns(#"Duplicated Column3",{{"Date - Copy", Date.Year, Int64.Type}}),
#"Renamed Columns5" = Table.RenameColumns(#"Extracted Year",{{"Date - Copy", "Year"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns5", "Financial Quarter", each if [Financial Period] <= 3 then "Q1" else if [Financial Period] >= 10 then "Q4" else if [Financial Period] = 4 then "Q2" else if [Financial Period] = 5 then "Q2" else if [Financial Period] = 6 then "Q2" else if [Financial Period] = 7 then "Q3" else if [Financial Period] = 8 then "Q3" else if [Financial Period] = 9 then "Q3" else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column1", "Custom", each [Financial Quarter] & [Year]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Financial Year", each Date.Year(Date.AddDays([Date],184))),
#"Renamed Columns6" = Table.RenameColumns(#"Added Custom1",{{"Date", "EOM Date"}}),
#"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns6", "EOM Date", "EOM Date - Copy"),
#"Renamed Columns7" = Table.RenameColumns(#"Duplicated Column4",{{"EOM Date - Copy", "Date"}}),
#"Inserted Start of Month" = Table.AddColumn(#"Renamed Columns7", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Removed Columns2" = Table.RemoveColumns(#"Inserted Start of Month",{"Date"}),
#"Renamed Columns8" = Table.RenameColumns(#"Removed Columns2",{{"Start of Month", "Date"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns8",{"Calendar Month", "EOM Date", "Month", "Year", "Financial Period", "Financial Quarter", "Financial Year"}),
#"Renamed Columns9" = Table.RenameColumns(#"Removed Columns3",{{" Accrued Expenses - PJM GoPNG", "Accrued Expenses - PJM GoPNG"}})
in
#"Renamed Columns9"let
Source = Excel.Workbook(File.Contents("C:\Users\61431\Desktop\GCC\BI\FY21 Budget for Power BI.xls"), null, true),
Transposed1 = Source{[Name="Transposed"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Transposed1, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account", type date}, {"Consulting Revenue (202)", type number}, {"Cost Recoveries (203)", type number}, {"Interest Income (270)", Int64.Type}, {"Management Fee Income (299)", Int64.Type}, {"Other Revenue (260)", Int64.Type}, {"Total Income", type number}, {"Consulting Expenses (302)", type number}, {"Contractors (304)", type number}, {"Direct Annual Leave Expense (309)", Int64.Type}, {"Direct Superannuation (308)", type number}, {"Direct Wages and Salaries (307)", type number}, {"Discounts Provided (303)", Int64.Type}, {"Wage offset to align budget & actuals (310)", Int64.Type}, {"Total Cost of Sales", type number}, {"Gross Profit", type number}, {"Gain/(Loss) on Sale of Assets (250)", Int64.Type}, {"JobKeeper (261)", Int64.Type}, {"PAYG Cashboost ATO (311)", Int64.Type}, {"Total Other Income", Int64.Type}, {"Advertising (400)", Int64.Type}, {"Annual Leave Expense (476)", Int64.Type}, {"Bad Debt Expense (402)", Int64.Type}, {"Bank Fees (404)", type number}, {"Bank Revaluations (497)", Int64.Type}, {"Bookkeeping (408)", Int64.Type}, {"Conferences (410)", Int64.Type}, {"Consulting & Accounting (412)", Int64.Type}, {"Dividend offset account to align actuals vs budget (501)", Int64.Type}, {"Dividend Paid (500)", Int64.Type}, {"Donations (419)", Int64.Type}, {"Entertainment (420)", Int64.Type}, {"Equipment Expense (426)", Int64.Type}, {"Filing Fees (480)", Int64.Type}, {"Freight & Courier (425)", Int64.Type}, {"FX Gain or Loss (999)", Int64.Type}, {"General Expenses (429)", Int64.Type}, {"Income Tax Expense (950)", type number}, {"Insurance (433)", type number}, {"Interest Expense (437)", type number}, {"Legal expenses (441)", Int64.Type}, {"Light, Power, Heating (445)", Int64.Type}, {"Marketing Expenses (447)", Int64.Type}, {"Motor Vehicle Expenses (449)", type number}, {"Motor Vehicle Fuel (450)", type number}, {"New Office Costs (452)", Int64.Type}, {"Office Expenses (453)", type number}, {"Outgoings (468)", Int64.Type}, {"Printing & Stationery (461)", Int64.Type}, {"Realised Currency Gains (499)", Int64.Type}, {"Recruitment Costs (470)", Int64.Type}, {"Registrations (464)", Int64.Type}, {"Rent (469)", Int64.Type}, {"Repairs and Maintenance (473)", Int64.Type}, {"Subscriptions (485)", Int64.Type}, {"Superannuation (478)", type number}, {"Telephone & Internet (489)", Int64.Type}, {"Training (474)", type number}, {"Travel - International (494)", Int64.Type}, {"Travel - National (493)", Int64.Type}, {"Uniforms (495)", Int64.Type}, {"Unrealised Currency Gains (498)", Int64.Type}, {"Wages and Salaries (477)", Int64.Type}, {"Website Expenses (475)", Int64.Type}, {"Total Operating Expenses", type number}, {"Directors Drawings (418)", Int64.Type}, {"Total Overheads", Int64.Type}, {"Amortisation (401)", type number}, {"Depreciation (416)", Int64.Type}, {"Total Depreciation", type number}, {"Total Expenses", type number}, {"Net Profit", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Account", "Date"}})
in
#"Renamed Columns"Still can't get it. I have tried to get the max value but just can't figure it out. It doesn't seem to let me select two fields to be able to get the max.
I want to get the max of consulting revenue from transposed data and consulting revenue (202) from budget. Once I have this, I understand how I can get that in to the gauge field.
I then want to create the gauge colour field but again, don't know how to do that.
If you need more data than above, please let me know what you need me to send and how best to send it.
Thanks. Nikki
Hi NikkiSavage
Thanks for your reply.
Yes, we need more data than above, could you share the .pbix file? or some sample data if they can fully illustrate your question. Besides, you can read this article, follow the steps in the article to describe your problem
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.