Help with DAX syntax - measure to create a percentage

[SteveODea]

Hi there!

I am struggling with my DAX syntax and was hoping that someone could help me.

I have a measure which counts rows but includes a filter option

CountOfItems = CALCULATE(DISTINCTCOUNT('Table1'[Ref]), LEFT('Table1'[Desc],3) <> "Acc")

This works fine and when I put this into a matrix visualisation this gives me everything I need.

I now need to create a table underneath this which shows the percentages of the total in each field.

Pretty simple, and I know I need to divide by the total figure (4,169) and use ALL or ALLSELECTED, but as I'm new to DAX, I'm struggling with the syntax a bit

This is what I've attempted, but is not working

%cOUNT = CALCULATE(DISTINCTCOUNT('Table1'[Ref]), LEFT('Table1'[Desc],3) <> "Acc")/

CALCULATE(ALL(DISTINCTCOUNT('Table1'[Ref])), LEFT('Table1'[Desc],3) <> "Acc")

[Mariusz · Accepted solution]

Hi @SteveODea 

What @Greg_Deckler  said or adjust your syntax to.

%cOUNT = 
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT( 'Table1'[Ref] ), 
        LEFT( 'Table1'[Desc], 3 ) <> "Acc"
    ),
    CALCULATE(
        DISTINCTCOUNT( 'Table1'[Ref] ), 
        ALL( 'Table1' ),
        LEFT('Table1'[Desc],3) <> "Acc"
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[SteveODea]

Thanks @Mariusz you're a legend!

I wasn't sure where to put the ALL function, and even now I know where it should go I don't really understand it, but it works!

I guess that's a battle for another day.

I also changed ALL to ALLSELECTED as I have some filters on the page but it's what I want and I have full control over the formatting too!

Cheers buddy!

[Greg_Deckler]

Try click the drop down arrow for the field in the Values area and choose Show As | Percent of Grand Total or Percent of Row/Column Total.

[SteveODea]

Great thanks @Greg_Deckler  that's a useful tip to know, which works.

The only problem I have now is I cannot format the %age as shown. All the percentages show with 2 decimal places but I am unable to change this.

I have tried selecting "Value Decimal Places" under Field Formatting in the Format Tile, but it appears to be stuck on "Auto"

Any ideas how to change this?