Forum Discussion
Help with dax
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.
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.
2 Replies
Did you mean to select "Percentage of Column Total" ?
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.
