Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Comparison to minimum

Hello Everyone, I have a data table that I use that has the revenue dollars per mile by hour in the day. What I have done so far is calculated the revenue per mile in each hour of the day and I want...
Need Help
v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, Anonymous 

According to your requirement, you can try these measures:

Overall min dollar per mile = 
MINX(ALL('Table'),[Dollars per mile])
Delta = 
SUMX('Table',[Dollars per mile]-[Overall min dollar per mile])

 

And you can get what you want, like this:

 

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Everyone,

     

    Apologies in advance if this is a duplicate response, I thought I had replied, but I do not see it.  

     

    I have tried the suggestions given, but did not have any luck.  I am going to provide some more details on my data hoping it helps.  I have about 200K rows, with each row being a dispatch.  The dataset includes columns such as $$$ per trip, distance per trip, and hour of the day of the trip.  

     

    I've taken that data set and made a matrix in PBI which shows the total $$$, total distance, and the dollars per mile (dpm).  See below the first six hours of data where the first four columns are calculated correctly in PBI.  I have also shown two additional columns which I added for the purpose of the example but were calculated in Excel.  These last two columns are the columns I want to add to matrix for my PBI report.

     

    hour in daysum of $$$sum of distance (miles)dpmdpm min (DESIRED RESULT)delta (DESIRED RESULT)
    0 $    150,468.34             31,0694.84.20.6
    1 $    109,293.39             23,0204.74.20.5
    2 $       78,192.71             16,8794.64.20.4
    3 $       58,451.66             12,8504.54.20.3
    4 $       48,309.60             11,2374.34.20.1
    5 $       48,203.37             11,3574.24.20.0
    6 $       82,614.76             18,4774.54.20.2

     

    When I used the suggestions given to me, instead of getting 4.2 for dpm (column 5), I was getting the lowest individual trip dpm ($$$/mile), but I want the lowest of the (sum of $$$/sum of distance) for the entire hour.

     

    Hopefully this is clearer.

     

    Appreciate the help.

    • v-robertq-msft's avatar
      v-robertq-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hey, @klew31

      Depending on your requirement, you can try these measures:

      dpm min = 
MINX(ALL('Table'),[dpm])
      Delta = 
SUMX('Table',[dpm]-[dpm min])

      And you can get whatever you want, like this:

      You can download my test pbix file here

      Best regards

      Qin Community Support _Robert Team

      If this post helps,then consider Accepting it as the solution to help other members find it faster.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      Does this measure work?

      =MINX(ALL(Data[hours in day]),[dpm])