topic Re: Help with dax in DAX Commands and Tips

Help with dax

siac161 — Mon, 08 Apr 2024 22:04:23 GMT

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.

Re: Help with dax

lbendlin — Tue, 09 Apr 2024 01:11:47 GMT

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

Re: Help with dax

Anonymous — Thu, 11 Apr 2024 06:57:12 GMT

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.