Create colouring on bar chart depending on conditions of a column and the count
Hi, I am looking for help to create colouring on a bar chart depending on certain conditions. I have a bar chart which displays the column Performance. The values in this column are categories. The graph shows the grand total count (%) of each category. I would like to do conditional formatting colouring on the bars with the following conditions: Meets Expectations is colour orange when, Meets Expectations + Exceeds Expectations > 90 Marginal is coloured yellow when, Marginal + Below Expectations > 40 Exceeds is coloured green when, Exceed Expectations > 30 Below is coloured red when, Below Expectations> 15 Since I am doing a grand total count of each category, I have tried to create measures for each performance category to count the grand total, but realized this does not work as it will not take the other measures into account. This is what I did: I created this conditional formatting measure 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") Where [Exceeds%], [Meets%], [Marginal%], [Below%] are measures I created to try to capture the grand total % count. The calculations I used for each were similar: Exceeds% = [Count ExceedExpectations]/ COUNTROWS(FILTER(ALLSELECTED('A Table'),'A Table'[PerformanceLevel] IN {"Below Expectations","Marginal","Meets Expectations","Exceed Expectations" }))*100 After doing this and testing, I realized by doing the separate measures for each performance category, it was not taking the other categories into account. When I tried to sum them in the conditional formatting measure, one of them would be counted as 0. Perhaps I am overcomplicating and there is a much simple way to do this. This is a link to sample workbook: google drive How can I do this? I would appreciate any guidance and help. Thank you
Strange! Unclear circulrar reference
I have 2 calculated columns: Weeks Bin = // calc col to order dispatch weeks in bins to prepare for a histogram SWITCH( TRUE(), [dispatch weeks] <= 2 , "≤ 2" , [dispatch weeks] <= 4 , "2-4" , [dispatch weeks] <= 6 , "4-6" , [dispatch weeks] <= 8 , "6-8" , [dispatch weeks] <= 10, "8-10" , [dispatch weeks] <= 13, "10-13", [dispatch weeks] <= 26, "13-26", [dispatch weeks] <= 39, "26-39", [dispatch weeks] <= 52, "39-52", "> 52" ) Index = // calc col to introduce a parameter for sorting the bins as they are not numerical SWITCH( Stocks[Weeks Bin], "≤ 2" , 1, "2-4" , 2, "4-6" , 3, "6-8" , 4, "8-10" , 5, "10-13", 6, "13-26", 7, "26-48", 8, "48-52", 9, "> 52" , 10 ) Power BI desktop throws an error for the [Index] definition! : "A circular dependency was detected: Stocks[Weeks Bin], Stocks[Index], Stocks[Weeks Bin]." I do not see any circular reference! If I delete the [Index] column, [Weeks Bin] works fine, and [Index] column is just numerical indexing based on [Weeks Bin] values. Also [dispatch weeks] is a measure and has nothing to do with Stocks[Index]: dispatch weeks = // number of weeks covered by stock based on the previous 13-wk dispatch average) DIVIDE( SUM(Stocks[Qty]) + 0, [dispatch qty Rav] ) dispatch qty Rav = VAR period = // gets a period which ends the previous Saturday and extends back for 13 weeks DATESINPERIOD( DimDate[Date], MAX(DimDate[Date]) - WEEKDAY(MAX(DimDate[Date])), // previous Saturday -7 * 13, // extend backwards 13 weeks DAY ) VAR total = // totals the quantity over the 13 weeks period CALCULATE( [dispatch qty], period ) VAR numofweeks = // number of weeks, will return < 13 if period hits date table starting date CALCULATE( ROUNDUP( DIVIDE( COUNTROWS(period), 7 ), 0 ), period ) RETURN IF( HASONEVALUE(Items[Item Code]), // if one item is selected, calculate the dispatch quantity 13wk running average ROUNDUP( DIVIDE( total, numofweeks ), 0 ))Add calculated column by finding Difference between selected row and previous month data
I need to find the difference of clicks to each query in a perticlar date In example : I wan to find difference betwen clicks for savings account ranking for URL 1 in May and Apr. I want the value in CLicks difference column against the Query 1. Should i use Unique IDs ? I ahve created a Unique Query ID and i have tried using the following formula it gave error ClicksDifference = VAR CurrentQueryID = 'DS Page And Query Wise'[UniqueID] VAR UnderscorePosition = FIND("_", CurrentQueryID, 1, LEN(CurrentQueryID)) // Find the first underscore position // Extract current month substring after the last underscore VAR CurrentMonth = MID( CurrentQueryID, UnderscorePosition + 1, // Start from the character after the underscore LEN(CurrentQueryID) - UnderscorePosition // Length of the substring ) // Assuming you have a mapping table for months like in the previous example VAR MonthMapping = DATATABLE ( "MonthName", STRING, "MonthNumber", INTEGER, { {"January", 1}, {"February", 2}, {"March", 3}, {"April", 4}, {"May", 5}, {"June", 6}, {"July", 7}, {"August", 8}, {"September", 9}, {"October", 10}, {"November", 11}, {"December", 12} } ) // Lookup current month number VAR CurrentMonthNumber = LOOKUPVALUE(MonthMapping[MonthNumber], MonthMapping[MonthName], CurrentMonth) // Calculate previous month number VAR PreviousMonthNumber = IF(CurrentMonthNumber = 1, 12, CurrentMonthNumber - 1) // Lookup previous month name VAR PreviousMonthName = LOOKUPVALUE(MonthMapping[MonthName], MonthMapping[MonthNumber], PreviousMonthNumber) // Replace current month with previous month in QueryID VAR PreviousQueryID = SUBSTITUTE(CurrentQueryID, "_" & CurrentMonth, "_" & PreviousMonthName) // Calculate previous month clicks VAR PreviousClicks = CALCULATE( SUM('DS Page And Query Wise'[Url Clicks]), FILTER( 'DS Page And Query Wise', 'DS Page And Query Wise'[UniqueID] = PreviousQueryID ) ) // Calculate current month clicks VAR CurrentClicks = SUM('DS Page And Query Wise'[Url Clicks]) // Calculate clicks difference RETURN IF( ISBLANK(PreviousClicks), BLANK(), CurrentClicks - PreviousClicks ) Second Query I used Clicks Difference = VAR SelectedClicks = 'DS Page And Query Wise'[Url Clicks] VAR SelectedLOB = 'DS Page And Query Wise'[LOB ] VAR SelectedQuery = 'DS Page And Query Wise'[Query] VAR SelectedLandingPage = 'DS Page And Query Wise'[Landing Page] VAR SelectedDate = 'DS Page And Query Wise'[Date (Year Month)] VAR PriorMonth = EOMONTH(SelectedDate, -1) VAR PreviousMonthClicks = CALCULATE( SUM('DS Page And Query Wise'[Url Clicks]), FILTER( 'DS Page And Query Wise', 'DS Page And Query Wise'[Date (Year Month)] = PriorMonth && 'DS Page And Query Wise'[LOB ] = SelectedLOB && 'DS Page And Query Wise'[Query] = SelectedQuery && 'DS Page And Query Wise'[Landing Page] = SelectedLandingPage ) ) RETURN IF( 'DS Page And Query Wise'[LOB ] = SelectedLOB && 'DS Page And Query Wise'[Query] = SelectedQuery && 'DS Page And Query Wise'[Landing Page] = SelectedLandingPage && 'DS Page And Query Wise'[Date (Year Month)] = PriorMonth, SelectedClicks - PreviousMonthClicks, BLANK() )
