% Difference from Total Average
Hi experts, I need your help. I would like to create a table below, metric = views, average views, total average views and difference from average. This is my pbi file: https://drive.google.com/file/d/15sJq2aL0X-t3745OiMW5ZfG1vzz_GGoT/view?usp=drive_link I have tried so many measures, but It wasnt dynamic, in my original dashboard I have lots of slicers, I need to work together. Thank you for your help
Issues conditional formatting coloring with a sum of measures
Hi community, I have been working on this visual for some time now. I’ve had members help me along the way which has saved me greatly. I am having issues with implementing conditional formatting on a bar chart depending on measures - I have been trying to create a type of bar chart where each bar may be coloured depending on measures. This is how the bar chart looks like: I have used this for conditional formatting: Conditional Formatting Measure = Switch(TRUE(), SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Meets " && [Meets%]+[Exceeds%] > 90, "orange", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Marginal" && [Marginal%] + [Below%] > 40, "yellow", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Exceeds" && [Exceeds%] > 30, "green", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Below" && [Below%] > 15, "red", "grey") When there is a sum used in the condition, the bar does not change colour – the sum is not taken into account. I have many instances where Marginal+Below>40 and Meets+Exceeds>90 but the bar doesn't change colour. Here is a link to a sample workbook: google drive I would really appreciate any help and guidance on this. I don't understand why the sum is being ignored.
Individual Row Colors in Matrix
Hello! I am attempting to color individual rows of a matrix. I understand this is not currently a feature in Power BI, but using this YouTube video, I was able to find a work around. I used the below measure, which I used as Conditional Formatting, to color the rows based on the HEX color listed in each row of the measure. However, this seems to exclude cells with a zero/blank value. How can I modify the below measure to ensure all cells for each row have the same formatting? For instance, "In Transit Receipts" shows many white cells because they have no value. I would like these cells to be the same light blue color as the cells with a value. Metric Colors = SWITCH( TRUE(), MAX('APO Monthly'[MetricNew])="Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="Sales Order","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Sales Orders","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="DI Forecast","#e6e6e6", MAX('APO Monthly'[MetricNew])="Stock Transfer (Out)","White", MAX('APO Monthly'[MetricNew])="SAP Suggested Buy","#e6e6e6", max('APO Monthly'[MetricNew])="In Transit Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Confirmed Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Additional Confirmed Receipts (Inc STO In)","#d9e1f2", MAX('APO Monthly'[MetricNew])="Total Demand","#09124f", MAX('APO Monthly'[MetricNew])="Total Receipts","#09124f", max('APO Monthly'[MetricNew])="Stock On Hand (Projected)","#055201", "White" ) Thank you!
Calculate YOY by ignoring the lowest granularity?
Hi PBI community, So I have some sales data that comes in the below format. The Periods TY and LY means "This Year" and "Last Year", and I'm trying to find the YOY sales. The measure usually works but I think the transaction ID is confusing PBI and it's not calculating the difference. Is there any way to edit my DAX to ignore transaction id and get yoy for the skus? I only need transactions for distinct counts (my real data has a product hierarchy assoaciated with the sku so i need distinct counts and YOY sales for those too). Sales YOY = var ty = CALCULATE(SUM('Table'[sales]),'Table'[Period]="TY") var ly = CALCULATE(SUM('Table'[sales]),'Table'[Period]="LY") RETURN ty-ly
Average of last 6 months data in cards
Hi All , I am getting error message below when i am using Averagex DAX for my scenario. I want to get the average values in card visual from the last 6 months data in line chart. Below the error message and the data visuals (the one with title of Project Code Creations) Please help to fix this issue for me 😞 Good Friday .. TIA 🙂 TR Dinesh
Need help with a DAX measure (Headcount)
Hello everyone! I'm excited to make my first post on this forum and to be a part of the Power BI community. I've been exploring Power BI for about a year and am encountering a challenge with the following formula: Headcount Status = VAR SelectedDate = MAX('DateDimensionTable'[Date]) VAR LatestStatusPerEmployee = CALCULATETABLE( SUMMARIZE( /*to summarize by employee ID*/ 'FactsTable', 'FactsTable'[EMPLOYEE_EMPLOYEEID], "LatestStatusChange", MAX('FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), "LatestStatus", CALCULATE(MAX('FactsTable'[EMPLOYMENTSTATUS_LONGNAME]), /*is the column where the status are*/ USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]), FILTER(ALL('FactsTable'), 'FactsTable'[EMPLOYEE_EMPLOYEEID] = EARLIER('FactsTable'[EMPLOYEE_EMPLOYEEID]) && 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP] == [LatestStatusChange])) ), USERELATIONSHIP('DateDimensionTable'[Date], 'FactsTable'[EMPLOYEEEMPLOYMENTSTATUS_CREATEDTIMESTAMP]) ) VAR CountCondition = FILTER( LatestStatusPerEmployee, [LatestStatus] IN {"Status 1", "status2", "status3"} ) RETURN COUNTROWS(CountCondition) My objective is to create a dynamic headcount measure that tracks employee status changes over time. For instance, using a slicer, if I filter to December 31st, an employee who was in "Status 1" in January and moved to "Status 2" in December should be reflected correctly in the breakdown. However, I'm facing two main issues: The overall headcount seems incorrect and tends to decrease as I go back in time, even though there should be variations. In visualizations, employee statuses aren't updating correctly. For example, if an employee's status changed in August, that status remains fixed in all subsequent views. In my model, the primary connection is between the date in my dimension table and the original hire date in the fact table. I've attempted to use a USERELATIONSHIP in the DAX to incorporate a link with the status creation timestamp, but this hasn't resolved the issue. I'm open to any questions or suggestions for further clarification. Thank you in advance for your help!New Matrix Column for Total Percentages
Hi All, I've been stuck working on creating another column in my matrix chart to capture the total percentages (shown in the red). Does anyone have any suggestions on how to solve for this or where to begin with the DAX formula? Thank you in advance for your help! Status Group 1 Group 2 Total Open/Closed % Closed 75 35 110 73.33 % Open 25 15 40 26.67% Total 100 50 150 100 %
