Help with dax

[siac161]

Hi I'm trying to build a clustered chart. 

The Y axis are a column that have Regions.
The X axis is a distinct count of ID's
The legends are the Years

The issue that I have is that I want to show the percentage of unique ID per year and region. Keeping other visualization filters, such as state, product category etc.

Region	2023	2024
1	25	30
2	30	15
3	35	10
4	40	40
Total	130	95

So for example region 1 should show 19.23% for 2023 and 31.58% for 2024. But If I use the Distinct count option to "show as a percentage of the grand total" it doesn't make sense. I

I created the next formula. 
Percentage per Year =
VAR TotalCountPerYear =
CALCULATE(DISTINCTCOUNT(Table[ID]), ALLEXCEPT(Table, Table[Year]))

VAR ClusterCount =
DISTINCTCOUNT(TableName[ID])

RETURN
DIVIDE(ClusterCount, TotalCountPerYear)

But I can't make the total remain 130 or 95. 

[Anonymous · Accepted solution]

Hi, @siac161

Maybe you can try the following DAX 

Percentage per Year = 
VAR TotalCountPerYear = CALCULATE(DISTINCTCOUNT(Table[ID]), ALL(Table), VALUES(Table[Year]))
VAR ClusterCount = DISTINCTCOUNT(Table[ID])
RETURN DIVIDE(ClusterCount, TotalCountPerYear)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Did you mean to select "Percentage of Column Total" ?