I am really struggling with what seems simple enough - compare distinct count totals (that have been

[Krissy]

I have been playing around with (unsuccessfully) in trying to get the comparison (Diff) to be compared to the grand total for the year, the data is all in the one column for the year i.e. year column contains both 2021 and 2022 and i need the grand total distinct count for student rego numbers per electorate (that i need to place into top 10 by payment which i have this part) and then i need a comparison column from the full years grand total compared to the electorate total for each year?

the below DAX works to a degree - but is not splitting and comparing the total to the year by the year if that makes sense and no amount of rewording or googling has provided me a decent answer, tried percentage and that did not work either

I did manage a way to get the result but i feel this was a more conveluded way as i had to create a measure to then use that measure which gave me the results

(on another report so first i created the split data to then use the results as the reference in this formula to then get the % difference: 

Stud Cnt by A/T =

DIVIDE(

    DISTINCTCOUNT(agreement[reg_no]),

    CALCULATE(DISTINCTCOUNT(agreement[ta_type]), ALL(organisation[organisation_nm]

)))

There has to be a way to simply get the percentage difference from the distinct count sums to the full overall count sums (i.e. column total diff to row) - not sure if the image is workingsnip of current report and the results in reference

[technolog]

The DAX formula you provided calculates the distinct count of student registration numbers and divides it by the distinct count of all student registration numbers across all organizations.

To achieve your goal, you need to:

Calculate the distinct count of student registration numbers for each electorate for a specific year.
Calculate the grand total of distinct student registration numbers for the entire year.
Compare the two values to get the percentage difference.
Here's a way to do it:

First, let's create a measure that calculates the distinct count of student registration numbers for each electorate for a specific year:

DistinctCountByElectorateYear =
DISTINCTCOUNT(agreement[reg_no])
Next, let's create a measure that calculates the grand total of distinct student registration numbers for the entire year:

GrandTotalDistinctCountYear =
CALCULATE(
DISTINCTCOUNT(agreement[reg_no]),
ALL(agreement),
VALUES(agreement[year])
)
The VALUES(agreement[year]) ensures that the calculation is done for the specific year in context.

Now, to get the percentage difference, you can create a measure that divides the distinct count for each electorate by the grand total for the year:

PercentageDifference =
DIVIDE(
[DistinctCountByElectorateYear],
[GrandTotalDistinctCountYear]
) - 1
This will give you the percentage difference for each electorate compared to the grand total for the year. You can then multiply the result by 100 to get the percentage.

I hope this helps! Remember, DAX can be a bit tricky, especially when dealing with context and filters. It's all about understanding the context in which your measures are being calculated and ensuring that you're comparing the right values.