Edit DAX measure to ignore certain scliers

[Invesco]

Hi Experts

How do i modify the following dax , so the measure ignores [ProductName] and [Bussines] slicer.

Measure

Days # =

Calculate(SUM(act_rates_activity_volume_and_cf_days[data_value]),

                        FILTER(VALUES(act_rates_activity_volume_and_cf_days),

act_rates_activity_volume_and_cf_days[activity_type_1] ="customer_facing_day"))

[Mikelytics · Accepted solution]

Hi @Invesco 

Lets start with the FacingDay measure 

NumberOfCustomerFacingDays =
CALCULATE(
   DISTINCTCOUNT(MasterFactTable[Date]),
   ALL(MasterFactTable[ProductName]),
   ALL(MasterFactTable[Business])
)

now the number of products sold:

NumberOfProductsSold =
CALCULATE(
   COUNTROWS(MasterFactTable)
)

Now the Rate:

Rate=
DIVIDE([NumberOfProductsSold],NumberOfCustomerFacingDays])

In the end it depends on how you build the visuals in Power BI Report view and which filter you apply.

If something doies not work please provide more details/screenshots whart ever.

Because "not working" does not help to come nearer to the solution. 🙂

Further topics where I think would be needed to build a solid solution.

On which date are you filtering?

Are multiple employees in the table? If yes, is the employee ID in the table?

Are aggregated (e.g. for multiple employees) working?

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Invesco]

Incorrectly accepted...
my customer facing days is

Days # =

Calculate(

   SUM(act_rates_activity_volume_and_cf_days[data_value]),

   FILTER(

      VALUES(act_rates_activity_volume_and_cf_days),

      act_rates_activity_volume_and_cf_days[activity_type_1] ="customer_facing_day"

   ),

   ALL(act_rates_activity_volume_and_cf_days[product_name_]),

   ALL(act_rates_activity_volume_and_cf_days[activity_type_1])

)

and this goes to zero when i use slicer Product_Name

[Mikelytics]

HI @Invesco 

Sorry, I have here many questions.

1. Are you using calculated columns or measures?

2. You say aemployee MIGHT have 5 days. Where do you get the days from?

3. how can the rate be bigger than 1. I mean if you have 5 customer facing days and in this days you sell 12 products then its still 1 and not 12/5 or am I missunderstanding something?

4. When the oranges are sold on the same day is that counted as 2 customer facing days?

5. How do you want top visualize the data in Power BI Report View

6. Are you using a proper date table?

Best regards

Michael

[Invesco]

1. Are you using calculated columns or measures? Measure

2. You say aemployee MIGHT have 5 days. Where do you get the days from? See image i sent it has 5 days this has to be customer facing days based on column [activity_type_1] ="customer_facing_day"

3. how can the rate be bigger than 1. I mean if you have 5 customer facing days and in this days you sell 12 products then its still 1 and not 12/5 or am I missunderstanding something? not the rate cal is 12/5 in the business 

4. When the oranges are sold on the same day is that counted as 2 customer facing days? - not that has to be the overall customer facing days for that employee in our image and example 5

5. How do you want top visualize the data in Power BI Report View... in a card.

6. Are you using a proper date table? - no - i have one master FACT Table

[Mikelytics]

Hi @Invesco 

Please try:

Days # =
Calculate(
   SUM(act_rates_activity_volume_and_cf_days[data_value]),
   FILTER(
      VALUES(act_rates_activity_volume_and_cf_days),
      act_rates_activity_volume_and_cf_days[activity_type_1] ="customer_facing_day"
   ),
   ALL(Table[ProductName]),
   ALL(Table[Business])
)

You have to replace "table" with the table which contains the column.

If this does not work please show on an example which behaviour is missing in your result.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Invesco]

Hi Mikelytics - not  work....

Calculate(SUM(act_rates_activity_volume_and_cf_days[data_value]),

                        FILTER(VALUES(act_rates_activity_volume_and_cf_days),

act_rates_activity_volume_and_cf_days[activity_type_1] ="customer_facing_day"))

This value that is calculated has to remain fixed - based on the activity_type_1 ie on how many customer facing days there are against a employee, 

So an employee might have 5 customer facing days in a week......
depending on the product he sells, the rate calculation changes
See table below

johns overall rate is (f2f) is 12/5 (5 customer facing days)
but john also sells oranges so his oranges customer facing days = 2/5 =0.4

Thats my issue..