Need help in DAX Measure

[Anonymous]

Experts, I need your help to fix the DAX formula. The below measure should sum up all the values as denominator in the measure. Actually, I am not able to sum those values as Link ID column datatype in text. Below is the measure written with hardcoded value, but I would like to see that in dynamically should sum up in denominator.

% Link ID = DISTINCTCOUNT('Query 1'[Link ID])/206 (This should replace with the Total Sum Value of yellow box highligted one)

Thanks,

Bala

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

% Link ID = divide( DISTINCTCOUNT('Query 1'[Link ID]), calculate( DISTINCTCOUNT('Query 1'[Link ID]), allselected())

https://learn.microsoft.com/en-us/dax/allselected-function-dax

[Anonymous · Accepted solution]

Hi @Anonymous,

I'd like to suggest you add a variable with summarize function to aggregate these ID at first, then you can simply summary these results to calculate with current id:

% Link ID =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Query 1' ),
        [Category],
        "DC LID", DISTINCTCOUNT ( 'Query 1'[Link ID] )
    )
RETURN
    DISTINCTCOUNT ( 'Query 1'[Link ID] ) / SUMX ( summary, [DC LID] )

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

% Link ID = divide( DISTINCTCOUNT('Query 1'[Link ID]), calculate( DISTINCTCOUNT('Query 1'[Link ID]), allselected())

https://learn.microsoft.com/en-us/dax/allselected-function-dax