Forum Discussion

arad33's avatar
arad33
Frequent Visitor
3 years ago

Measure to filter on slicer

Hi Everyone 

 

I have a sales report containing a measure that apportions all of the associated costs based on the percentage of our production time.

 

I have a slicer based on our Fiscal calendar and a Dropdown to select customer with the below relationships

 

Sales Report [Customer ID] > CustomerDim [Dustomer ID] Many To One

Sales Report [Invoice Date] > DimCYDate [ dtShort] Many To One

 

When I set my measure to be

 

Percentage Of ESH = DIVIDE(SUM(SalesReport[ESH Calc]),CALCULATE(SUM(SalesReport[ESH Calc]),ALLSELECTED(SalesReport)))
 
This works fine while I am returning everything but when I filter by Customer it still calculates the Percentage of ESH to 100% and apportions all total costs to that Customer.
 
If I change the Measure to 
Percentage Of ESH = DIVIDE(SUM(SalesReport[ESH Calc]),CALCULATE(SUM(SalesReport[ESH Calc]),ALL(SalesReport)))
 
On my page not filtered by Customer but filtered by date it drops my Percentage of ESH to 17.20% as I now know its looking at the full sales report.  But this in turn obviously apportions less of the cost.
 
So ideally I need the Percentage of ESH to take into account the dates from the slicer when calculating the ESH Calc so that I get Percentage of ESH = 100% when filtered by date and unfiltered by customer but then for example 22% When filtered by Customer.
 
I hope all of that makes sense and appreciate any help anyone can offer as I have tried so many combos.
Need Help

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    3 years ago

    arad33  Have you tried ALLSELECTED(Date) ? If I understand correctly I think that should work for you??

    • arad33's avatar
      arad33
      Frequent Visitor
      3 years ago

      Thanks for the reply so if I take it to

       

      Percentage Of ESH = DIVIDE(SUM(SalesReport[ESH Calc]),CALCULATE(SUM(SalesReport[ESH Calc]),ALLSELECTED(DimCYDate)))
       
      This returns 100% across every line and apportions 100% of the costs across each line
      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        3 years ago

        arad33 Apologies, you'll also need to add a calculate modifier on the customer table too now (sorry I didn't read your requirements closely enough). Try:

         

        Percentage Of ESH = DIVIDE(SUM(SalesReport[ESH Calc]),CALCULATE(SUM(SalesReport[ESH Calc]),ALLSELECTED(DimCYDate)), ALL( CustomerDim ))