Forum Discussion

paololito's avatar
paololito
Helper I
3 years ago
Solved

Help With SubTotal

Hello,

 

I would like your support regarding this issue .

The table below is a matrix , I created a mesure to have the % of diference between the previous week (Diff. to LW).

Unfortunatelly the last column is blank . 

my goal is to have the % between the total of flights of the current compared to the Week -1 .

 

Do you have an Idea ?

 

Thanks 

 

 

 
Need Help
  • paololito's avatar
    paololito
    3 years ago

    amitchandak  Sorry I forgot the measure .

    M_NumberOfFlight = COUNTROWS('Rapport 1')
    M_LW NumberOfFlights =
    Var _SelectedWeek = SELECTEDVALUE('Rapport 1'[NUMERO SEMAINE])
    Var _SelectedHandler = SELECTEDVALUE('Rapport 1'[HANDLER])
    Var _SelectedOperator = SELECTEDVALUE('Rapport 1'[OPERATOR])
    Var _SelectedDir = SELECTEDVALUE('Rapport 1'[DIR])
    Var _SelectedYear = SELECTEDVALUE('Rapport 1'[MVT DATE + HR UTC].[Year])
    Var _res = CALCULATE( COUNTROWS('Rapport 1'),FILTER(ALL('Rapport 1'),   'Rapport 1'[NUMERO SEMAINE] =  _SelectedWeek -1) , 'Rapport 1'[HANDLER] = _SelectedHandler,'Rapport 1'[OPERATOR] = _SelectedOperator, 'Rapport 1'[DIR] = _SelectedDir, 'Rapport 1'[MVT DATE + HR UTC].[Year] = _SelectedYear)
    RETURN _res

     

     

    % Diff. to LW =
    IF( ISBLANK( [M_LW NumberOfFlights] ),
        BLANK(),
            DIVIDE(  [M_NumberOfFlight] - [M_LW NumberOfFlights]  ,  [M_LW NumberOfFlights] , 0 ) )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    paololito ,  For that we need to check the measure please share.

     

    Please see my Rank column method can help

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

    These measures can help
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

     

    Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

    • paololito's avatar
      paololito
      Helper I
      3 years ago

      amitchandak  Sorry I forgot the measure .

      M_NumberOfFlight = COUNTROWS('Rapport 1')
      M_LW NumberOfFlights =
      Var _SelectedWeek = SELECTEDVALUE('Rapport 1'[NUMERO SEMAINE])
      Var _SelectedHandler = SELECTEDVALUE('Rapport 1'[HANDLER])
      Var _SelectedOperator = SELECTEDVALUE('Rapport 1'[OPERATOR])
      Var _SelectedDir = SELECTEDVALUE('Rapport 1'[DIR])
      Var _SelectedYear = SELECTEDVALUE('Rapport 1'[MVT DATE + HR UTC].[Year])
      Var _res = CALCULATE( COUNTROWS('Rapport 1'),FILTER(ALL('Rapport 1'),   'Rapport 1'[NUMERO SEMAINE] =  _SelectedWeek -1) , 'Rapport 1'[HANDLER] = _SelectedHandler,'Rapport 1'[OPERATOR] = _SelectedOperator, 'Rapport 1'[DIR] = _SelectedDir, 'Rapport 1'[MVT DATE + HR UTC].[Year] = _SelectedYear)
      RETURN _res

       

       

      % Diff. to LW =
      IF( ISBLANK( [M_LW NumberOfFlights] ),
          BLANK(),
              DIVIDE(  [M_NumberOfFlight] - [M_LW NumberOfFlights]  ,  [M_LW NumberOfFlights] , 0 ) )
  • paololito's avatar
    paololito
    Helper I
    3 years ago

    I worked on a solution but I don't understand why the Row subtotal is 0.00 % 

     

     

     

    perhaps my Dax code is incorrect  .

     

    M_NumberOfFlight total for OPERATOR =
    Var _SelectedWeek = SELECTEDVALUE('Rapport 1'[NUMERO SEMAINE])
    Var _SelectedHandler = SELECTEDVALUE(Handler[HANDLER] )
    Var _SelectedOperator = SELECTEDVALUE(OPERator[OPERATOR])
    Var _SelectedYear = SELECTEDVALUE('Rapport 1'[MVT DATE + HR UTC].[Year])

    VAR _TotalOfRowsCurrent = CALCULATE( COUNTROWS('Rapport 1'),FILTER(ALL('Rapport 1'),   'Rapport 1'[NUMERO SEMAINE] =  _SelectedWeek ) , 'Rapport 1'[HANDLER] = _SelectedHandler,'Rapport 1'[OPERATOR]= _SelectedOperator,  'Rapport 1'[MVT DATE + HR UTC].[Year] = _SelectedYear)

    VAR _TotalOfRowsPrevious = CALCULATE( COUNTROWS('Rapport 1'),FILTER(ALL('Rapport 1'),   'Rapport 1'[NUMERO SEMAINE] =  _SelectedWeek -1) , 'Rapport 1'[HANDLER] = _SelectedHandler,'Rapport 1'[OPERATOR]= _SelectedOperator,  'Rapport 1'[MVT DATE + HR UTC].[Year] = _SelectedYear)

    Var _percent = IF(FORMAT(DIVIDE(_TotalOfRowsCurrent - _TotalOfRowsPrevious, _TotalOfRowsPrevious), "0.00%") = BLANK() ,"0.00%",FORMAT(DIVIDE(_TotalOfRowsCurrent - _TotalOfRowsPrevious, _TotalOfRowsPrevious), "0.00%") )

    RETURN
     
    IF( ISINSCOPE('Rapport 1'[DIR]),[M_NumberOfFlight] , _percent)
     
     
    Cany ou Help me ?
     
    Thanks for your support