Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Date slicer does not work properly in graph

In a measure I am working with dates selected from a slicer to compute proportion of a the whole period my boxes are full. I have a table with columns: ID, start time, end time. There can be multiple same IDs in the ID column. 

sample data

See the count days in slicer measure:

 

the proportion is calculated as follows: SUM(difference in days)/(days in slicer * count of distinct IDs)

and works fine when displayed in a card for overall average

 

However, when I slice the data, the result gets distorted by the Count days in slicer measure because the measure also selects the date for each individual ID, which is weird and unwanted. How can one make it select the max and min values of selected date for ALL IDs to count with?

 

The slicer measure selects days for each individual ID and therefore the calculation is wrong.

 

I've been trying to handle this problem for quite some time, any help appreciated!

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi,

     

    >>the proportion is calculated as follows: SUM(difference in days)/(days in slicer * count of distinct IDs)

     

    >>However, when I slice the data, the result gets distorted by the Count days in slicer measure because the measure also selects the date for each individual ID, which is weird and unwanted. How can one make it select the max and min values of selected date for ALL IDs to count with?

     

    Could you show us what the measures above(red color) look like? Which measures do you want to be not affected by the date slicer? Maybe you need to use the ALL function.

     

    Could you please share your desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

     

    Best Regards,

    Amy

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, 

       

      I am not sure whether I understand your response correctly. The desired outcome is the portion calculated correctly, that is: count the days selected in the slicer (24 for example) and then for each ID calculates the portion of days a box has been fully loaded (difference in days).

       

      2What the calculation does now is, that from the slicer it counts days for each ID (could be 2 insted of 24 for example) and then calculates the portion, which is why my graph shows values above 100%. (Because a box does not necesarily have an information for every day within the slicer).

       

      What I desire is, hence, for the calculation to work with the overall count of days in slicer for every ID and not to count days for each ID.

      Hope this helps,
      Matt

      v-xicai