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 helpingCumulative unique count based on sales values-DAX
Hi expert, I am stucking on to calculate cumulative unique count in DAX month on month basis some criteria, please help me to get this done. Thanks a ton in advance. Icey Tanushree_Kapse VahidDM Anonymous amitchandak pbig administrator PBICommunity PBCommunity Raw data and summary below. Please help me on this. ThanksOptimizing a measure filter with calculated table
Hi, I am trying to optimize my measure below by replacing the filter conditions with Calculatetable. I was able to replace my date filter. But replacing the other filter on GL Master is not giving desired results. GL Master has multiple hierarchy levels. Though it returns correct value at the summation level, but repeats the sum value when report is crossfiltered at any GL Master hierarchy: YTD Amount - 1 = var max_date = max('Date'[Date]) var BS = CALCULATE([Amount (period) New],CALCULATETABLE('Date',all('Date'[Date]),'Date'[Date] <= max_date),FILTER('Gl Master', left('GL Master'[Account Head],3) in {"Ass","Equ","Lia",BLANK()})) var PnL = CALCULATE([Amount (period) New],DATESYTD('Date'[Date],"03/31"),FILTER('Gl Master',left('GL Master'[Account Head],3) in {"Inc","Exp","Tax","Oth","Mem","Exc"})) return BS+PnL YTD Amount - 2 = var max_date = max('Date'[Date]) var BS = CALCULATE([Amount (period) New],CALCULATETABLE('Date',all('Date'[Date]),'Date'[Date] <= max_date),CALCULATETABLE('Gl Master', left('GL Master'[Account Head],3) in {"Ass","Equ","Lia",BLANK()})) var PnL = CALCULATE([Amount (period) New],DATESYTD('Date'[Date],"03/31"),CALCULATETABLE('Gl Master',left('GL Master'[Account Head],3) in {"Inc","Exp","Tax","Oth","Mem","Exc"})) return BS+PnL Result: Entity Code Financial Year(Date) Account Head(GL Master) YTD Amount - 1 YTD Amount - 2 111 FY21 Assets 4509817901 180025.73 111 FY21 Equity -12495100815 180025.73 111 FY21 Expenses 1823651648 180025.73 111 FY21 Income -2602949869 180025.73 111 FY21 Liability 8547147510 180025.73 111 FY21 Memo 231583709.2 180025.73 111 FY21 Other Comprehensive Income 180025.73 111 FY21 Tax expense -13970058.9 180025.73 111 FY21 (blank) 180025.73 FY21 Total 180025.73 180025.73 111 Total 180025.73 180025.73 Grand Total 180025.73 180025.73 Is there any way to make Calculatetable work dynamically with report slicers or optimize this measure further with desired results (as in YTD Amount -1)? FYI: I have tried ALLSELECTED two ways: 1) CALCULATETABLE(ALLSELECTED('Gl Master'), left('GL Master'[Account Head],3) in .................... 2) CALCULATETABLE('Gl Master',ALLSELECTED(), left('GL Master'[Account Head],3) in .................... Thanks in advance, Shailee.
