Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Measure to change with Slicer

‎11-02-2022 07:58 AM

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.

 

DateEMPREFLocDefCapped2
24 October 2022123Home7
17 August 2022123Home7
30 June 2022123Home7.4
5 October 2022123Office7.533333
9 June 2022123Office7.533333
23 May 2022123Home7.4
12 April 2022123Office7.533333
14 June 2022123Home7.4
28 April 2022123Office7.516667
22 April 2022123Home7.4
31 May 2022123Home7.4
20 May 2022123Office7.416667
13 June 2022123Home7.4
30 May 2022123Home7.4
4 October 2022123Home7
21 July 2022123Home7
15 July 2022123Home7
26 May 2022123Home7.4
13 April 2022123Office7.633333
17 October 2022123Home7
10 October 2022123Home7
25 October 2022123Home7
19 October 2022123Office7.933333
13 October 2022123Office8.966667
12 October 2022123Office7.883333
6 October 2022123Office7.933333
30 September 2022123Home7
29 September 2022123Home7
28 September 2022123Home7
10 August 2022123Office8.133333
22 July 2022123Office8.133333
20 July 2022123Office7.983333
19 July 2022123Home7
18 July 2022123Home7
28 June 2022123Office7.966667
20 June 2022123Home7.4
1 June 2022123Office7.916667
24 May 2022123Office7.9
19 May 2022123Home7.4
18 May 2022123Home7.4
17 May 2022123Office8
12 May 2022123Office8.316667
27 April 2022123Office8.65
20 April 2022123Office8.483333
7 April 2022123Office7.9
27 June 2022123Home7.4
21 April 2022123Home7.4
8 August 2022123Home7
21 June 2022123Home7.366667
25 April 2022123Home7.366667
20 October 2022123Office7.083333
19 August 2022123Home7
27 May 2022123Office7.083333
11 April 2022123Home7.016667
10 May 2022123Home7.35
14 October 2022123Home7
9 August 2022123Home7
15 August 2022123Home7
25 May 2022123Home7.266667
14 July 2022123Home7
17 June 2022123Home7.233333
7 June 2022123Home7.233333
19 April 2022123Home7.216667
22 August 2022123Home7
11 August 2022123Office7.066667
5 April 2022123Home7.066667
6 July 2022123Home7
23 August 2022123Home7
16 May 2022123Home7.283333
29 April 2022123Home7.283333
7 July 2022123Home7
21 October 2022123Office7.2
18 August 2022123Office7.2
6 April 2022123Office7.2
18 October 2022123Home7
3 October 2022123Home7
11 October 2022123Home7
24 August 2022123Office6.983333
26 August 2022123Office6.95
26 July 2022123Home6.95
1 July 2022123Office6.95
11 May 2022123Home6.9
25 August 2022123Home6.933333
25 July 2022123Home6.933333
13 May 2022123Office6.933333
1 April 2022123Office6.933333
7 October 2022123Home6.883333
16 August 2022123Home4.833333
12 August 2022123Home6.833333
27 July 2022123Office6.466667
13 July 2022123Home6.816667
12 July 2022123Home5.766667
11 July 2022123Home6.166667
8 July 2022123Office6.216667
29 June 2022123Home6.666667
10 June 2022123Home6.616667
8 June 2022123Home6.883333
3 June 2022123Office6.516667
26 April 2022123Home6.883333
14 April 2022123Home3.766667
8 April 2022123Home6.116667
9 September 2022123Client7
8 September 2022123Client7
7 September 2022123Client7
6 September 2022123Client7
5 September 2022123Client7
2 September 2022123Client7
1 September 2022123Client7
31 August 2022123Client7
30 August 2022123Client7
29 August 2022123Client7
1 August 2022123Home0
6 June 2022123Home0
2 May 2022123Home0
15 April 2022123Home0
18 April 2022123Home0
4 May 2022123Home0
3 May 2022123Home0
27 September 2022123Home0
26 September 2022123Home0
23 September 2022123Home0
22 September 2022123Home0
21 September 2022123Home0
20 September 2022123Home0
19 September 2022123Home0
16 September 2022123Home0
15 September 2022123Home0
14 September 2022123Home0
13 September 2022123Home0
12 September 2022123Home0
5 August 2022123Home0
4 August 2022123Home0
3 August 2022123Home0
2 August 2022123Home0
29 July 2022123Home0
28 July 2022123Home0
5 July 2022123Home0
4 July 2022123Home0
24 June 2022123Home0
23 June 2022123Home0
22 June 2022123Home0
16 June 2022123Home0
15 June 2022123Home0
2 June 2022123Home0
9 May 2022123Home0
6 May 2022123Home0
5 May 2022123Home0
4 April 2022123Home0

 

This should be the end result

Vito_0-1667470482744.png

 

Thank you for helping

Message 1 of 3
324 Views
0
2 REPLIES 2
Anonymous
Not applicable

‎11-03-2022 02:28 AM

Hi @Anonymous ,

 

Please share the expected result.

 

Best Regards,

Jay

Message 2 of 3
279 Views
0
Anonymous
Not applicable
In response to Anonymous

‎11-03-2022 03:15 AM

Hi,
thank you for your reply. I have updated the post now.

Message 3 of 3
273 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All