Measure to change with Slicer
Good afternoon, I need to create a measure tha show the ratio below according to the a slicer selected by the user. In my case the slicer must be the date. I tried the formula below but without success. EMPREF is also used for RSL purposes. Summary3 = var SelDate = SELECTEDVALUE(Special[Date]) var Summary = CALCULATETABLE(SUMMARIZE(Special,Special[EMPREF],"Home",CALCULATE(Sum(Special[Capped2]),Special[LocDef] = "Home"),"Office/Client",CALCULATE(SUm(Special[Capped2]),filter(Special, CONTAINSSTRING( Special[LocDef], "Office") || CONTAINSSTRING( Special[LocDef], "Client")))), Special[Date] <= SelDate) var ratio = SELECTCOLUMNS(Summary,"Office/Client",[Office/Client])/(SELECTCOLUMNS(Summary,"Office/Client",[Home])+SELECTCOLUMNS(Summary,"Office/Client",[Office/Client])) return ratio This is an example of the data. Date EMPREF LocDef Capped2 24 October 2022 123 Home 7 17 August 2022 123 Home 7 30 June 2022 123 Home 7.4 5 October 2022 123 Office 7.533333 9 June 2022 123 Office 7.533333 23 May 2022 123 Home 7.4 12 April 2022 123 Office 7.533333 14 June 2022 123 Home 7.4 28 April 2022 123 Office 7.516667 22 April 2022 123 Home 7.4 31 May 2022 123 Home 7.4 20 May 2022 123 Office 7.416667 13 June 2022 123 Home 7.4 30 May 2022 123 Home 7.4 4 October 2022 123 Home 7 21 July 2022 123 Home 7 15 July 2022 123 Home 7 26 May 2022 123 Home 7.4 13 April 2022 123 Office 7.633333 17 October 2022 123 Home 7 10 October 2022 123 Home 7 25 October 2022 123 Home 7 19 October 2022 123 Office 7.933333 13 October 2022 123 Office 8.966667 12 October 2022 123 Office 7.883333 6 October 2022 123 Office 7.933333 30 September 2022 123 Home 7 29 September 2022 123 Home 7 28 September 2022 123 Home 7 10 August 2022 123 Office 8.133333 22 July 2022 123 Office 8.133333 20 July 2022 123 Office 7.983333 19 July 2022 123 Home 7 18 July 2022 123 Home 7 28 June 2022 123 Office 7.966667 20 June 2022 123 Home 7.4 1 June 2022 123 Office 7.916667 24 May 2022 123 Office 7.9 19 May 2022 123 Home 7.4 18 May 2022 123 Home 7.4 17 May 2022 123 Office 8 12 May 2022 123 Office 8.316667 27 April 2022 123 Office 8.65 20 April 2022 123 Office 8.483333 7 April 2022 123 Office 7.9 27 June 2022 123 Home 7.4 21 April 2022 123 Home 7.4 8 August 2022 123 Home 7 21 June 2022 123 Home 7.366667 25 April 2022 123 Home 7.366667 20 October 2022 123 Office 7.083333 19 August 2022 123 Home 7 27 May 2022 123 Office 7.083333 11 April 2022 123 Home 7.016667 10 May 2022 123 Home 7.35 14 October 2022 123 Home 7 9 August 2022 123 Home 7 15 August 2022 123 Home 7 25 May 2022 123 Home 7.266667 14 July 2022 123 Home 7 17 June 2022 123 Home 7.233333 7 June 2022 123 Home 7.233333 19 April 2022 123 Home 7.216667 22 August 2022 123 Home 7 11 August 2022 123 Office 7.066667 5 April 2022 123 Home 7.066667 6 July 2022 123 Home 7 23 August 2022 123 Home 7 16 May 2022 123 Home 7.283333 29 April 2022 123 Home 7.283333 7 July 2022 123 Home 7 21 October 2022 123 Office 7.2 18 August 2022 123 Office 7.2 6 April 2022 123 Office 7.2 18 October 2022 123 Home 7 3 October 2022 123 Home 7 11 October 2022 123 Home 7 24 August 2022 123 Office 6.983333 26 August 2022 123 Office 6.95 26 July 2022 123 Home 6.95 1 July 2022 123 Office 6.95 11 May 2022 123 Home 6.9 25 August 2022 123 Home 6.933333 25 July 2022 123 Home 6.933333 13 May 2022 123 Office 6.933333 1 April 2022 123 Office 6.933333 7 October 2022 123 Home 6.883333 16 August 2022 123 Home 4.833333 12 August 2022 123 Home 6.833333 27 July 2022 123 Office 6.466667 13 July 2022 123 Home 6.816667 12 July 2022 123 Home 5.766667 11 July 2022 123 Home 6.166667 8 July 2022 123 Office 6.216667 29 June 2022 123 Home 6.666667 10 June 2022 123 Home 6.616667 8 June 2022 123 Home 6.883333 3 June 2022 123 Office 6.516667 26 April 2022 123 Home 6.883333 14 April 2022 123 Home 3.766667 8 April 2022 123 Home 6.116667 9 September 2022 123 Client 7 8 September 2022 123 Client 7 7 September 2022 123 Client 7 6 September 2022 123 Client 7 5 September 2022 123 Client 7 2 September 2022 123 Client 7 1 September 2022 123 Client 7 31 August 2022 123 Client 7 30 August 2022 123 Client 7 29 August 2022 123 Client 7 1 August 2022 123 Home 0 6 June 2022 123 Home 0 2 May 2022 123 Home 0 15 April 2022 123 Home 0 18 April 2022 123 Home 0 4 May 2022 123 Home 0 3 May 2022 123 Home 0 27 September 2022 123 Home 0 26 September 2022 123 Home 0 23 September 2022 123 Home 0 22 September 2022 123 Home 0 21 September 2022 123 Home 0 20 September 2022 123 Home 0 19 September 2022 123 Home 0 16 September 2022 123 Home 0 15 September 2022 123 Home 0 14 September 2022 123 Home 0 13 September 2022 123 Home 0 12 September 2022 123 Home 0 5 August 2022 123 Home 0 4 August 2022 123 Home 0 3 August 2022 123 Home 0 2 August 2022 123 Home 0 29 July 2022 123 Home 0 28 July 2022 123 Home 0 5 July 2022 123 Home 0 4 July 2022 123 Home 0 24 June 2022 123 Home 0 23 June 2022 123 Home 0 22 June 2022 123 Home 0 16 June 2022 123 Home 0 15 June 2022 123 Home 0 2 June 2022 123 Home 0 9 May 2022 123 Home 0 6 May 2022 123 Home 0 5 May 2022 123 Home 0 4 April 2022 123 Home 0 This should be the end result Thank you for helpingCreate a Slicer based on values from 2 different tables
Hi Everyone, I have created the follwing Bar Chart based on values from 2 different tables. Dark blue is from Table1 and Light Blue is from Table2 and the DateTable is separate and I have made the relationship of these 2 tables with DateTable Now, I wanted to create s slicer based on these two different values. For example; Slicer1 = Total sales, Slicer2 = Total instock If I click on Total sales, it shows only the Dark blue bar chart and if I click on Total instock it shows only light blue one and if I do not click anywhere it must shows the below one (Combine one) Thank you
Converting 2 Calculated columns into a Slicer
I have a report that have 2 pages (Large Companies and Non Large Companies) 1 page has a calculated column that filters "Large Companies" --- Calculated column to pull up the "Large Companies" in Rows - If(ISBLANK(RELATED('Company Rollup'[Large Company Rollup])) || RELATED('Company Rollup'[Large Company Rollup])="","Other",RELATED('Company Rollup'[Large Company Rollup])) the 2nd page has a calculated column that filters "Not-Large Companies" Calculated column to pull up the "Non Large Companies" in Rows - if(ISBLANK(RELATED('Company Rollup'[Parent Market])) || RELATED('Company Rollup'[Parent Market])="",Company[Company],RELATED('Company Rollup'[Parent Market])) Filter on the page that filters out Large Companies - Not Large = if(Company[Companu Rollup ]="Other",TRUE(),FALSE()) I'm attempting to place both of the calculated columns into a slice so I will only have 1 page, then utilize a slicer with a 1. Large Companie 2. Non Large Companies 3. All Companies
Slicer not getting applied to measure in column!
Hi Experts, Scenario: Sample Data: Data Model : The relationships are one to many (filtering : single) Sample report: ------------------------------------------------------------------------------------------- Student Count is a calculated field which shows 0 against week where student was not present: IF(distinctcount[student] <> 1, 0, 1) Ideally, on clicking the Area Slicer (blue bars), the student under it should show up, which is happening until I include the Stundent Count measure in the table. The slicer is not getting applied to the measure. Meaning, it is showing all the students irrespective of the value clicked on Area Slicer. Could someone please comment how to make the slicer applicable to the measure? Just to note, the final output will show just the absent records: Any help would be appreciated.
Using a slicer and filtering if the Number is between two numbers
I have a data table with a Start Year Column and End year Column I want to be able to Create a slicer that when I select a year it finds all rows between or equal to the start year and below or equal to the end year. Im not sure if creating a seperate key table with the years that I would be able to select from and linking it by some comparison. Year Comp 2 is just a seperate Table with possible years I would like to be able to slice by. Table 1 Table 2
Dynamically Pick Min Date based on slicer's selection
Hi friends, I am still new to Power BI, hope you guys can provide me some guidance. Here is my situation: I would like to pick min date for each level of percentile (low, mid, and high) from each provider separately based on the condition that the percentage in PctAvailable column is greater than the corresponding percentage in slicer (on the leftside of the screenshot below). I created three columns, LowDate, MidDate, and HightDate to store the calculated min dates. Also, I added columns LowPCT, MidPCT, and HighPCT to show the selected percents from slicers for demenstration purposes. Using provider 403 as example, the LowDate is 10/13, MidDate is 12/20, and HighDate is also 12/20 based on the current selections. However, the min dates of each level don't change when different values are selected. It looks like the min dates are selected using the min percentage of each level, not dynamically update based on the selection. I created three tables containing the values of each slicer, called LowSelection, MidSelection, and HighSelection. Here are the formulas that I used for the calculation (using Low percentile calcs as example) LowPCT= if(HASONEFILTER(LowSelection[Low]),LOOKUPVALUE(LowSelection[Low],LowSelection[Low],values(LowSelection[Low]))) LowDate= CALCULATE(min('Table'[SlotDatetimeDTS]),FILTER('Table','Table'[ProviderID]=EARLIER('Table'[ProviderID])&&'Table'[PctAvailable]>[LowPCT])) Please let me know which parts do I have fix or adjust? Thank you so much for your time!How to create a Slicer based on a range of Calculated Measure
Hi , like a slicer that has a range of value, where upon selcting, it will pick a Measure that falls within the range. I tried going thru many examples in the internet but have not come a cross one that address my challenge. Imaging I have 2 tables (i) Country GDP (ii) Country Defence spending .. I create a measure of DefenceSpending / Country GDP = X% Using the attached file, the end result if to be able to show the following when I select the slicer What I am looking for is to select the slicer below and PowerBI table return only Country with measure within the selected range I have created a new table (without relationship), with the below 4 scenarios BUT i dont know how to make the slicer full the data I wanted SLICER 0% < 1.0% This should select Myamar, Malaysia, Pakistan, Brunei, Korea 1.0% < 2.0% This should select Philippine, Nepal, Bangladesh, Vietnam, New Zealand 2.0% < 3.0% This should select Sri Lanka, Japan, HongKong 3% or greater This should select Singapore, Thailand, China Taiwan, Indonesia & Australia
