Forum Discussion

coetseem's avatar
coetseem
Frequent Visitor
1 year ago
Solved

DAX Formula

Hi All,

 

I have a table with data for teams, the owner / month i am trying to create a formula that determine the % per user per team. I am trying to determine a DAX formula that calculates the Final % now the formula that i have work if no month or year is selected but once you select the month and year then the formula does not work.

 

Sample data

The Final % is what i am trying to get to:

TeamOwnerMonthCountFinal % 
Team1User1Feb-2526742% 
Team1User2Feb-2513622% 
Team1User3Feb-25559% 
Team1User4Feb-25437% 
Team1User5Feb-259415% 
Team1User6Feb-25345%629
Team1User1Jan-251009% 
Team1User2Jan-2550045% 
Team1User3Jan-2520% 
Team1User4Jan-2540036% 
Team1User5Jan-251009% 
Team1User6Jan-2520%1104
Team2User1Feb-2527740% 
Team2User2Feb-2514621% 
Team2User3Feb-25659% 
Team2User4Feb-25538% 
Team2User5Feb-2510415% 
Team2User6Feb-25446%689
Team2User1Jan-251109% 
Team2User2Jan-2551044% 
Team2User3Jan-25121% 
Team2User4Jan-2541035% 
Team2User5Jan-251109% 
Team2User6Jan-25121%1164

 

Current Results:

 

DAX Formula: 

 

Distinct Count by Team =
CALCULATE(
    DISTINCTCOUNT('Step 1 - Approval'[ID]),     
    ALLEXCEPT('Step 1 - Approval', 'Step 1 - Approval'[Team])     
)

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

% Distinct Count Contribution =
DIVIDE(
    DISTINCTCOUNT('Step 1 - Approval'[ID]),       
    [Distinct Count by Team],                      
    0  
                                          
)

 

TeamOwnerCount%
Team1User12442,56%
Team2User21491,56%
Team3User31431,50%
Team4User4250,26%
Team5User5620,65%
Team6User6270,28%
Team1User120,96%
Team2User220,96%
Team3User3146,73%
Team5User462,88%
Team1User1104,81%
Team2User211,00%
Team3User311,00%
Team4User411,00%
Team5User511,00%
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    coetseem First, calculate the total count per team for the selected month and year:

    DAX
    Total Count by Team =
    CALCULATE(
    SUM('Step 1 - Approval'[Count]),
    ALLEXCEPT('Step 1 - Approval', 'Step 1 - Approval'[Team], 'Step 1 - Approval'[Month])
    )

     

    Then, calculate the percentage contribution of each user per team:

    DAX
    % Contribution by User =
    DIVIDE(
    SUM('Step 1 - Approval'[Count]),
    [Total Count by Team],
    0
    )

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    coetseem First, calculate the total count per team for the selected month and year:

    DAX
    Total Count by Team =
    CALCULATE(
    SUM('Step 1 - Approval'[Count]),
    ALLEXCEPT('Step 1 - Approval', 'Step 1 - Approval'[Team], 'Step 1 - Approval'[Month])
    )

     

    Then, calculate the percentage contribution of each user per team:

    DAX
    % Contribution by User =
    DIVIDE(
    SUM('Step 1 - Approval'[Count]),
    [Total Count by Team],
    0
    )