HELP: % with dynamic Denominator

DAX DAX Calculations
1

Hello All,

For example, I have this simple example table that shows the Project, Team ID, Employee #, and Hours Worked.

(please refer to attached excel file and screenshot

Dax Example
Dax Example3253×1413 160 KB

I need to create a DAX measure that will calculate the percentage but the denominator must stay constant based on the Team ID. So the measure would look at the table, then filter down to Team ID = 301 and SUM up all the hours. Problem is for me, if there are other slicers and columns added to the table, the by filter context the denominator changes.

Please advise, how can i get my denominator to stay constant based on the selected Team ID and the sum of all those hours.

Hopefully this posts makes sense, please let me know if you need me to clarify anything. Thanks for all your help

Thanks.

2

Hi @kevinmach, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@kevinmach,

Welcome to the forum – great to have you here!

If I understand your question correctly, it can be addressed through a straightforward application of ALLSELECTED per the measure below:

Constant Denominator =

CALCULATE(
    [Total Hours],
    ALLEXCEPT(
       Data,
       Data[Team ID] 
    )
)

Based on the sample file I put together, you’ll see that using this approach the denominator and the percent of total hours remain unchanged regardless of the selections in the various slicers.

I hope this is helpful. Full solution file attached below.

  • Brian

image
image837×363 21 KB

eDNA Forum - Overriding Slicers.pbix (70.6 KB)

5

Thank you for your response. The example was helpful but I could not get it to work in my model. I have to come up with example data since the data I am working with is restricted. I noticed in your data model you broke out the dimensions into separate tables for Employee, Projects, Team (my model does the same) but what are those used for then, when you created the table you are still pull the fields from the Data table instead of from their respective dimension tables.

Using your formula my Constant Denominator is still changing when i start to use multiple slicers.

6

@kevinmach,

Yes, posting your PBIX file will be the best step forward at this point in getting to a specific solution.

Here’s a video on quick/easy ways to anonymize your data for posting on the forum:

https://youtu.be/VmWD7Ayw_NI

  • Brian
7

Thanks for posting your question @kevinmach. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

8

@kevinmach,

Okay, I wasn’t sure without seeing your PBIX what your model looked like or how your visuals were constructed. Based on your response above, give this revised measure a try:

Constant Denominator Revised = 

CALCULATE(
    [Total Hours],
   ALLEXCEPT(
    Data,
    Data[Team ID]
    ),
    VALUES( Data[Team ID] )
)