Using DAX to calculate a ratio based on slicers

[afonsoac]

Hello,

I have a table in Power BI which contains all the time tracking of my team members for the year. Each line contains the Activity, Date and Time Spent in the activity (there are more columns). Each team member can enter more than one line per day, if they work in more than one activity.

In my visuals, I have a table with all the activities listed, and I created another column that calculates a ratio: # of distinctive days one the activity was performed / # of total distinctive days.

Then I created a slicer that allows me to filter all the visuals by month.

The problem that I have is when I apply the slicer to a specific month (I added a column to calculate the month based on the date). Instead of calculating the ratio to the whole table, I would like the denominator to contain only the # of distinctive days for that month. Or in case I select 2 different months, to be the #of distinctive days for those 2 months. Currently my DAX formula only works if no filter is applied, and it goes as follows:

DAX Process Ratio = DIVIDE(DISTINCTCOUNT(TimeTracking[Day]);CALCULATE(DISTINCTCOUNT(TimeTracking[Day]);ALLEXCEPT(TimeTracking;TimeTracking[Day])))

Any idea on how could I do it?

Thank you.

[Anonymous]

Hi @afonsoac

I can immediately tell you that the model... well, is not the greatest. A good model is dimensional and this is how Power BI expects you to work. Deviate from this and you'll be in trouble rather sooner than later. For measuring time in your model you should have a dedicated date table that would be connected to your fact table, TimeTracking, on the Date/Day field. This is how it should be and it's the standard way of doing things right. Then your DAX calculation would be much less cumbersome and - what's more important - fast. Please read this to guide you: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

I wish I could give you the formula right now but I can't. Simply because I don't know the model and I don't know how to structure your dimensions. I just need to know which entities will be put in their own dimensions. I'd suspect that apart from Time, activities should also constitute their own dimension... but it's a mere guess. Once you create a good dimensional model, we can have a look 🙂

[AlB]

@afonsoac 

I'd need some more detailed info of what fields you are using in the visuals, slicer and a sample of the relevant tables.  Or perhaps you can share a simplified pbix with dummy data that reproduces the problem

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[afonsoac]

Hi @AlB , thank you for your answer

Now it shows everything 100%, for all the activities, which shouldn't be the case.

The field "Month" is the one I use to create the slicer per month, shouldn't it be somewhere in the formula?

Unfortunately I can't share the pbix since I'm creating it in my customer's laptop and I can't share files.

Thank you

[AlB]

Hi @afonsoac 

Is it really working as you have it, even withour filter?? Try this:

DAX Process Ratio =
DIVIDE (
    DISTINCTCOUNT ( TimeTracking[Day] );
    CALCULATE (
        DISTINCTCOUNT ( TimeTracking[Day] );
        ALLSELECTED ( TimeTracking[Day] )
    )
)

It this doesn't work, it'd be good if you can share the pbix . Or at least show a sample of your (relevant) tables

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers