How to keep the slicer when the slicer field is used as a groping column for other field?

[Anonymous]

Hello Friends,

I am having one issue with one of our reports. I need to fix it by the EOD. Please help me

If you look at the table above, driver BRIGES, PETER, MR has worked for 11.75 hours for both HARVEST and ONEFOR customer. In the next  second hour column I have splitted that 11.75 hours into hours worked for each CUSTOMER i.e 8.18 for HARVEST and 3.19 and 0.37 for ONEFOR using the below dax

%Customer = DIVIDE(SUM('GTR Job Logs'[Hours]),

CALCULATE(SUM('GTR Job Logs'[Hours]), ALLSELECTED('GTR Job Logs'[Vehicle Code],'GTR Job Logs'[Customer Code],'GTR Job Logs'[Route Code])))

Hours to Customers = 'TLX Measures'[%Customer] * SUM('EMP Timesheet'[Hours])

So far it's good. 

Now my client wants me to add CUSTOMER(HARVEST, ONEFOR) as slicer. If I add slicer now and select any one customer, I won't be able to see the hours as in the second hour column instead it takes the hours in first column . This is because the second hour column is calculated during the run time. 

But what I want is, when I click on the customer slicer, I should get the hours according to the second hour column. 

This is bit urgent. IF you could help me, I would be glad. Thank you so much. 

[v-lili6-msft]

hi  @Anonymous 

For your case, you need to use ALL insead of ALLSELECTED Function in your measure, please refer to this blog:

http://tinylizard.com/all-vs-allselected/

If you still have the problem, please share your sample pbix file for us have a test.

Regards,

Lin

[Anonymous]

Hello,

I have used ALL. IF I use it then my calculation Won't work as expected in the second hour column. 

[v-lili6-msft]

hi  @Anonymous 

Please share your sample pbix file for us have a test.

Regards,

Lin

[Anonymous]

Yes, I have sent you via Message. Thank you. 

[Ashish_Mathur]

Hi,

When you select HARVEST, only 1 row appears for Bridges, Peter Mr.  What exact figure do you want to see in both the hour columns?  Also, how can 2 measures have the same name (hours)?

[Anonymous]

Please FInd the PBIX file Link 

[Anonymous]

Hello,

The first hour column I have just added for a reference, which as taken from different table. Consequently, I am splitting that 11.75 hours into 8.18-> 3.19->.37 in the second hours column. Actually, I dont need that first hour column, I have just added for reference. 

When I click on HARVEST, I should see

When I click on ONEFOR, I should see

[Anonymous]

Hello,

For better understanding, what I am trying to do is. 

A employee PETER has worked for 11.75 hours according to EMP. Timesheet table. 

But his actual worked hours are 15.08 according to JOb Log table in which he worked for two different customers (harvest and onefor)

Now, I am trying to find the percentage of hours for each customer and then multiplying that with hours in the EMP timesheet table. Because I want % of hours for each customer from EMP timesheet table. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  I do not have the perfect solution but am sharing with whatever i have done so far so either you/someone else can modify/simplify my formulas.

As you can see in this screenshot, when no selection of Customer code is made, then the result for Bridges Peter is correct.

When HARVEST is selected in the slicer, then also the result of Bridges Peter is correct

However, when ONEFOR is selected in the slicer, then the result is incorrect

I think that the result is incorrect when ONEFOR is selected because of the additional filter conditions that you have applied in the filter pane - may be some of those are conflicting with the condition that we have selected.

Also, i think my formula is way too complicated and can definitely be simplified.

I look forward to someone else pitching in as well.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Please share only what is relevant for solving this problem.

[MFelix]

Hi @Anonymous ,

You are looking to keep the the hours split always the same correct so you can know what is the percentage of the time spent pwer customer right?

You need to use an ALL statement based on your customer column, however this depends also on the structure of your data.

Is it possible to share a sample of you model?