Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am very much a self taught noob at PowerBI and DAX, but have managed to build a basic P/L report that combines various measures via the switch operator to display a single column of data for a specific date
The full switch measure is below
Values to Display =
VAR DisplayItem = SELECTEDVALUE('Dim_PnL_Fields'[SubCategory])
VAR DisplayItem2 = SELECTEDVALUE('Dim_PnL_Fields'[Category])
RETURN
SWITCH (
TRUE (),
DisplayItem = "Restaurant - Beverage Sales", [Total Beverage Sales],
DisplayItem = "Restaurant - Food Sales", [Total Food Sales],
DisplayItem = "FOH Wages", [Total FoH Wages],
DisplayItem = "Kitchen Wages", [Total BoH Wages],
DisplayItem = "No of Covers", [Total Customers],
DisplayItem = "FOH Wage % of Total Restaurant Rev", ([Total FoH Wages]/[Total Sales])*100,
DisplayItem = "Kitchen Wages % of Food Revenue", ([Total BoH Wages]/[Total Food Sales])*100,
DisplayItem = "Total Salaries and Wages", [Total Wages],
DisplayItem = "Payroll Related Expenses", [Wage Expenses],
DisplayItem = "Discounts", [Total Discounts],
DisplayItem = "COS - Beverage", [Total Beverage Cost],
DisplayItem = "COS - Food", [Total Food Cost],
DisplayItem = "Total COS", [Total Costs],
DisplayItem = "COS % of Revenue", [COS % of Revenue],
DisplayItem = "Other Operating Expenses & Admin Expenses", [Other Expenses],
DisplayItem = "Net Profit/(Loss)", [Profit/Loss],
DisplayItem = "Net Profit/(Loss)%", [Profit/Loss Per],
DisplayItem2 = "Total Revenue", [Total Sales],
DisplayItem = "Total Wage %", (([Wage Expenses]+[Total Wages])/[Total Sales])*100
)
the next step is to format each value as either currency, % or whole number. I have found various threads that state to use a format tag around the measure being switched in.
However, as soon as I do that my matrix loses all of my filters.
Below is the updated DAX with a format for one measure, and what happens to my matrix
Values to Display =
VAR DisplayItem = SELECTEDVALUE('Dim_PnL_Fields'[SubCategory])
VAR DisplayItem2 = SELECTEDVALUE('Dim_PnL_Fields'[Category])
RETURN
SWITCH (
TRUE (),
DisplayItem = "Restaurant - Beverage Sales", Format([Total Beverage Sales], "$#.##"),
DisplayItem = "Restaurant - Food Sales", [Total Food Sales],
DisplayItem = "FOH Wages", [Total FoH Wages],
DisplayItem = "Kitchen Wages", [Total BoH Wages],
DisplayItem = "No of Covers", [Total Customers],
DisplayItem = "FOH Wage % of Total Restaurant Rev", ([Total FoH Wages]/[Total Sales])*100,
DisplayItem = "Kitchen Wages % of Food Revenue", ([Total BoH Wages]/[Total Food Sales])*100,
DisplayItem = "Total Salaries and Wages", [Total Wages],
DisplayItem = "Payroll Related Expenses", [Wage Expenses],
DisplayItem = "Discounts", [Total Discounts],
DisplayItem = "COS - Beverage", [Total Beverage Cost],
DisplayItem = "COS - Food", [Total Food Cost],
DisplayItem = "Total COS", [Total Costs],
DisplayItem = "COS % of Revenue", [COS % of Revenue],
DisplayItem = "Other Operating Expenses & Admin Expenses", [Other Expenses],
DisplayItem = "Net Profit/(Loss)", [Profit/Loss],
DisplayItem = "Net Profit/(Loss)%", [Profit/Loss Per],
DisplayItem2 = "Total Revenue", [Total Sales],
DisplayItem = "Total Wage %", (([Wage Expenses]+[Total Wages])/[Total Sales])*100
)
In this example I have only formatted the first measure for testing - Format([Total Beverage Sales], "$#.##").
But as soon as I save/validate the measure the report loses all values and seems to ignore my slicers.
This is probably something silly that I have missed in my DAX, but I cant for the life of me figure it out.
Thanks in advance!
@Westy85 , Check if field parameters can help then use that
Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |