DAX Formula

[coetseem]

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:

Team	Owner	Month	Count	Final %
Team1	User1	Feb-25	267	42%
Team1	User2	Feb-25	136	22%
Team1	User3	Feb-25	55	9%
Team1	User4	Feb-25	43	7%
Team1	User5	Feb-25	94	15%
Team1	User6	Feb-25	34	5%	629
Team1	User1	Jan-25	100	9%
Team1	User2	Jan-25	500	45%
Team1	User3	Jan-25	2	0%
Team1	User4	Jan-25	400	36%
Team1	User5	Jan-25	100	9%
Team1	User6	Jan-25	2	0%	1104
Team2	User1	Feb-25	277	40%
Team2	User2	Feb-25	146	21%
Team2	User3	Feb-25	65	9%
Team2	User4	Feb-25	53	8%
Team2	User5	Feb-25	104	15%
Team2	User6	Feb-25	44	6%	689
Team2	User1	Jan-25	110	9%
Team2	User2	Jan-25	510	44%
Team2	User3	Jan-25	12	1%
Team2	User4	Jan-25	410	35%
Team2	User5	Jan-25	110	9%
Team2	User6	Jan-25	12	1%	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  

)

Team	Owner	Count	%
Team1	User1	244	2,56%
Team2	User2	149	1,56%
Team3	User3	143	1,50%
Team4	User4	25	0,26%
Team5	User5	62	0,65%
Team6	User6	27	0,28%
Team1	User1	2	0,96%
Team2	User2	2	0,96%
Team3	User3	14	6,73%
Team5	User4	6	2,88%
Team1	User1	10	4,81%
Team2	User2	1	1,00%
Team3	User3	1	1,00%
Team4	User4	1	1,00%
Team5	User5	1	1,00%

[bhanu_gautam · Accepted solution]

@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
)

[coetseem]

Thank you, it worked.