Forum Discussion

m__m's avatar
m__m
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 : 100-33 = 67%

 

Can you please help me with the DAX to get the No% and Yes%?

I have to finally visualize the Yes% in a bar chart.

"DAX"
%grand total

3 Replies

  • johnt75's avatar
    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's avatar
      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's avatar
        johnt75
        Super User
        3 years ago

        For that you could replace ALLSELECTED('Table'[Market]) with ALL('Table'[Market])