Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Budget Variance - Multiple Slicers

Main Scope The main idea is to create a price variance between the selected reports, within the selected time frames. The user of the dashboard will: 1) Select the current budget and period 2) Se...
budget
SELECTEDVALUE
Slicer
Variances
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Please refer these measures set 1- Slicer 1, Sclicer 3 and Set 2 - Slicer 2 and Slicer 4

     

    Set 1 =
    var _max = maxx(allselected(Slicer3), Slicer3[Date])
    var _min = minx(allselected(Slicer3), Slicer3[Date])
    return
    CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer1[Report])
    && 'Total Data Table'[Dimdate] >=_min && 'Total Data Table'[Dimdate] <=_max))


    Set 2 =
    var _max = maxx(allselected(Slicer4), Slicer4[Date])
    var _min = minx(allselected(Slicer4), Slicer4[Date])
    return
    CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer2[Report])
    && 'Total Data Table'[Dimdate] >=_min && 'Total Data Table'[Dimdate] <=_max))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Please refer these measures set 1- Slicer 1, Sclicer 3 and Set 2 - Slicer 2 and Slicer 4

 

Set 1 =
var _max = maxx(allselected(Slicer3), Slicer3[Date])
var _min = minx(allselected(Slicer3), Slicer3[Date])
return
CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer1[Report])
&& 'Total Data Table'[Dimdate] >=_min && 'Total Data Table'[Dimdate] <=_max))


Set 2 =
var _max = maxx(allselected(Slicer4), Slicer4[Date])
var _min = minx(allselected(Slicer4), Slicer4[Date])
return
CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer2[Report])
&& 'Total Data Table'[Dimdate] >=_min && 'Total Data Table'[Dimdate] <=_max))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak Thank you, this helped me out a lot! I created the two sets within a measure with the following formula: 

Total Units =
var set1_max = maxx(allselected(Slicer3), Slicer3[Date])
var set1_min = minx(allselected(Slicer3), Slicer3[Date])
VAR units_set1 = CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer1[Report])
&& 'Total Data Table'[Date] >= set1_min && 'Total Data Table'[Date] <= set1_max))

var set2_max = maxx(allselected(Slicer4), Slicer4[Date])
var set2_min = minx(allselected(Slicer4), Slicer4[Date])
VAR units_set2 = CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report] in allselected(slicer2[Report])
&& 'Total Data Table'[Date] >= set2_min && 'Total Data Table'[Date] <= set2_max))

VAR Totalunits = calculate(sum('Total Data Table'[Invoiced Units]),FILTER('Total Data Table','Total Data Table'[Report] in allselected(slicer2[Report])
&& 'Total Data Table'[Date] >= set2_min && 'Total Data Table'[Date] <= set2_max ||
'Total Data Table'[Report] in allselected(slicer1[Report])
&& 'Total Data Table'[Date] >= set1_min && 'Total Data Table'[Date] <= set1_max))

RETURN
if(HASONEVALUE('Total Data Table'[Report]),Totalunits,units_set1-units_set2)
 
If I put the amount of units in a table for the selected budget and the selected time periods. Besides, it gives the variance between them.It is a beautiful dax code. Again, thank you very much!