Forum Discussion

Fusilier's avatar
Fusilier
Helper III
2 years ago
Solved

Help with Measure

Help with measure 3 hours ago Hi, Probably best to illustrate what I am trying to achieve in PBI with the tables below in.   My data table consists of individual responses to a survey. On...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    Fusilier,

     

    To start, I unpivoted the data in Power Query to look like this:

     

     

    Measures:

     

    Count = SUM ( 'All Responses (2)'[Answer Count] )
    Pct = 
DIVIDE (
    [Count],
    CALCULATE ( [Count], ALLSELECTED ( 'All Responses (2)'[Category] ) )
)
    Pct to Total Variance = 
VAR vPct = [Pct]
VAR vNumerator =
    CALCULATE (
        [Count],
        ALLSELECTED ( 'All Responses (2)' ),
        VALUES ( 'All Responses (2)'[Category] )
    )
VAR vDenominator =
    CALCULATE ( [Count], ALLSELECTED () )
VAR vCategoryTotal =
    DIVIDE ( vNumerator, vDenominator )
VAR vResult = vPct - vCategoryTotal
RETURN
    vResult

     

     

DataInsights's avatar
DataInsights
Super User
2 years ago

Fusilier,

 

To start, I unpivoted the data in Power Query to look like this:

 

 

Measures:

 

Count = SUM ( 'All Responses (2)'[Answer Count] )
Pct = 
DIVIDE (
    [Count],
    CALCULATE ( [Count], ALLSELECTED ( 'All Responses (2)'[Category] ) )
)
Pct to Total Variance = 
VAR vPct = [Pct]
VAR vNumerator =
    CALCULATE (
        [Count],
        ALLSELECTED ( 'All Responses (2)' ),
        VALUES ( 'All Responses (2)'[Category] )
    )
VAR vDenominator =
    CALCULATE ( [Count], ALLSELECTED () )
VAR vCategoryTotal =
    DIVIDE ( vNumerator, vDenominator )
VAR vResult = vPct - vCategoryTotal
RETURN
    vResult