Top Ten Table for current month

[JuanBolas]

Hello,

I'm trying to create a Top 10 table by Carrier for the Current with the following structure that I can later use for a Top 10 Bar Chart:

Carrier                  Trips this Month      Trips Previous Month

==================================

Carrier Name 1     Sum of Trips            Sum of Trips

Carrier Name 2

etc.

Other

My data is in a table called Cargo which summarized looks like this

Carrier ID  |    Carrier Name   |     Date    |    Origin    |    Destination    |    Cargo Weight    |    Cargo Value

Thanks in advance for your help.

[lbendlin · Accepted solution]

Here is the basic framework for what you are trying to achieve.  To add sparklines you would need to use SVG.

[lbendlin · Accepted solution]

here you go

[JuanBolas]

Thanks for your reply.

I've set up an Excel file with the sanitized data and an example of the output I'm aiming at. The example is done with pivot table and some subproduct formulas as well as a couple of bar charts made 'by hand' which wouldn't be the case in Power Bi.

Thanks in advance for your help.

[lbendlin · Accepted solution]

Here is the basic framework for what you are trying to achieve.  To add sparklines you would need to use SVG.

[lbendlin]

Sparky = 
// Static line color
VAR LineColor = "#01B8AA"
// Obtain overall min and overall max measure values when evaluated for each date
VAR SumData = CALCULATETABLE(SUMMARIZE(Cargo,[MonthYear],"ct",DISTINCTCOUNT(Cargo[Bill of Lading])),REMOVEFILTERS(Cargo[MonthYear]))
VAR YMinValue = MINX(SumData,[ct])
VAR YMaxValue = MAXX(SumData,[ct])
// "Date" field used in this example along the X axis
VAR XMinDate = MINX(SumData,[MonthYear])
VAR XMaxDate = MAXX(SumData,[MonthYear])
// Build table of X & Y coordinates and fit to 100 x 50 viewbox
VAR SparklineTable = ADDCOLUMNS(
    SumData,
        "X",INT(100 * DIVIDE([MonthYear] - XMinDate, XMaxDate - XMinDate)),
        "Y",INT(50 * DIVIDE([ct] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 50-[Y]," ",Cargo[MonthYear])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = 
    "data:image/svg+xml;utf8," & 
    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 50'>" &
     "<polyline fill='none' stroke='" & LineColor & 
     "' stroke-width='1' points='" & Lines & 
     "'/></svg>"
    
RETURN SVGImageURL

[JuanBolas]

Hi Ibendlin,

I hope this is my final question!  How can I reduce the height of the sparklines to half of what it is?  I tried changing where it says 100 to 50 but that doesn't work.

Thanks!

[JuanBolas]

I inserted the code as a mesaure and categorized it as image url.  This is what I get:

Maybe you cand send me the pbix file so I can check what I'm doing wrong.

Thanks in advance

[lbendlin · Accepted solution]

here you go

[JuanBolas]

Thank you! have been very kind in sharing your knowledge.

I have a final question.  Isn't it possible to used the 'built-in' sparklines to do this?

[JuanBolas]

I got it to work but the row height doesn't change... Is there a way to change it?

[lbendlin]

There is a row setting "Image height" ...

[lbendlin · Accepted solution]

No, because you want the sparklines to have a different filter context than the rest of the table. Remember you are filtering by a particular month.  The built-in sparkline would only show a single dot. (try it out)

[JuanBolas]

Thanks.  Tried and and got the single dot.  Thanks for the master lesson!

[JuanBolas]

Thanks! Where do I insert this code?

[lbendlin]

That would need to be a new measure, and it needs to be categorized as "Image URL".

NOTE: The code is rather blunt. I would recommend modifying it so the sparkline shows only the "last 12 months"  instead of everything.

[JuanBolas]

Thank you so mucho lbendlin! I will try to understand it and digest it.

I inserted it in a new Measure but as a result get text starting with "data&colon".

Thanks again!

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523