none
How to create line graph with values as a percentage of the total?? RRS feed

  • Question

  • Hi, I am trying to create a line graph that shows month-to-month trends of product category sales represented as a percentage of sales instead of the actual amount.  I need to do this so that overall sales volume doesn't create spikes in the graphs as the user it solely interested in the distribution of sales rather than the total amount.  Is this possible to do this in a mobile report graph if the base data is just a list of individual sales or would I need to create a separate/pre formatted data source in order to calculate the data?  I have been able to do it in Excel, but it required creating a pivot table and then an additional table to calculate the graph from.

    Here is an extremely simplified example of the data and the results

    E.g.If I have sales data by month for fruit sales.

    Month Fruit Qty
    Jan-16 Apple 10
    Jan-16 Orange 5
    Jan-16 Pear 1
    Feb-16 Apple 60
    Feb-16 Orange 45
    Feb-16 Pear 6
    Mar-16 Apple 30
    Mar-16 Orange 40
    Mar-16 Pear 3

    Creating line graph results in a spike in February sales and makes it difficult to recognize a change in Orange sale trends (especially if there was more data).

    However, as a percentage, the trend is much easier to recognize.

    Does anyone know how I could do this in the Mobile Repots Publisher?

    Thanks

    Wednesday, July 20, 2016 5:09 PM

All replies

  • Hi Phill,

    The easiest way to accomplish this is in your SQL statement:

    select
    	month,
    	fruit,
    	qty,
    	pct = qty / cast ( sum(qty) over (partition by month) as real)
    from
    	my_table
    order by
    	month,
    	fruit;

    Then use pct as the value you plot on your line.  Make sure you set your Y-axis to a percentage.

    I hope this helps.

    --DJAnsc


    DJAnsc

    Wednesday, July 20, 2016 7:24 PM
  • Awesome thank you. Do you think it will be a problem that my main dataset it a tabular model and the query is SQL? I haven't tried mixing dataset types in the mobile report viewer.
    Wednesday, July 20, 2016 8:52 PM
  • Hi Phill,

    I don't understand your concern.  The sample sql I provided is an example.  

    Assuming you are using SQL to generate your three columns ( month, fruit, qty ), I am suggesting you create a fourth column (pct) and then plot pct instead of qty.

    What do you mean by "tabular model and the query is SQL"?  There should be no difference to what you are doing now except the addition of a new column to the result set: pct


    DJAnsc

    Wednesday, July 20, 2016 9:30 PM
  • The line graph will be part of a dashboard in the Mobile Report Publisher which the dataset/source is based an SSAS Tabular Model.  You can't write SQL statements in a tabular model, it has to be DAX or MDX.

    To use the SQL statement, I'd have to create another dataset/source that connects to the SQL Database (instead of SSAS).

    Wednesday, July 20, 2016 9:56 PM