Forum Discussion

Frequent Visitor

3 years ago

Need help with DAX

I need to display No% and Yes%. I have market and sales count. I need to display No% and Yes%. No% =( Sales Count/Grand Total ) * 100 - Ex : (122/370)*100 = 33% Yes% = 100 - No% - Ex :...

"DAX"

%grand total

johnt75

Super User

3 years ago

You can create a measure like

No % =
IF (
    ISINSCOPE ( 'Table'[Market] ),
    VAR CurrentValue =
        SUM ( 'Table'[Sales count] )
    VAR TotalValue =
        CALCULATE ( SUM ( 'Table'[Sales count] ), ALLSELECTED ( 'Table'[Market] ) )
    RETURN
        DIVIDE ( CurrentValue, TotalValue )
)

and then

Yes % = 1 - [No %]

Format both measures as percentages

m__m

Frequent Visitor

3 years ago

Thank you.. this worked!

Another issue - If i have a 'Market' slicer on the report page, and I select a market .. for example - "Central".

In this case, I just want to show the 'Central' data but with the same yes% of 67%. How to achieve that?

To simplify - the calculation % is still based on the complete data which market slicer should not affect but I am able to see just the data for cental and not others.

johnt75
Super User
3 years ago
For that you could replace ALLSELECTED('Table'[Market]) with ALL('Table'[Market])