Forum Discussion

zawlh's avatar
zawlh
Icon for Helper I rankHelper I
6 years ago
Solved

Calculating Percent Change Without Date/Time

Hey everyone,
Please help me with one issue. I would like to know the percent change of my sheet.
LINK is here https://docs.google.com/spreadsheets/d/1jIHEFFkpcD6pGQ3cvag0KSB3fqNMZPSZasTHZE2cJWU/edit#gid=0

I would like to use Course Name in X-AXIS and Count of User ID in Y-AXIS.

 

What I want in my report is to show percent change from Course1 to Course2, Course2 to Course3,Course3 to Course4,etc,...

When I search on google, all percent change formula include Date or Time. I saw Tableau can do percent change with built-in function. I hope one of specialist here can teach me how to do it in power BI.
With Thanks,
Power BI Newbie

  • az38's avatar
    az38
    6 years ago

    Hi zawlh 

    try a measure like

    Measure = 
var _curCourseCount = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Course Name]))
var _prevCourse = CALCULATE(MAX('Table'[Course Name]),FILTER(ALL('Table'),'Table'[Course Name]< SELECTEDVALUE('Table'[Course Name])))
var _prevCourseCount = CALCULATE(COUNTROWS('Table'),ALL('Table'),'Table'[Course Name]=_prevCourse)
return
IF(_prevCourseCount=0,BLANK(),DIVIDE(_curCourseCount,_prevCourseCount)-1)

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi zawlh 

    try a measure like

    Measure = 
var _curCourseCount = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Course Name]))
var _prevCourse = CALCULATE(MAX('Table'[Course Name]),FILTER(ALL('Table'),'Table'[Course Name]< SELECTEDVALUE('Table'[Course Name])))
var _prevCourseCount = CALCULATE(COUNTROWS('Table'),ALL('Table'),'Table'[Course Name]=_prevCourse)
return
IF(_prevCourseCount=0,BLANK(),DIVIDE(_curCourseCount,_prevCourseCount)-1)