Forum Discussion

RedTangerine's avatar
RedTangerine
Frequent Visitor
8 years ago
Solved

100% Stacked Column Chart Converted to Line Chart

  Currently I have 2 charts column and 100% stacked column. I need to show these as line charts with trend lines.       The first I can do resonably easy but I cannto figure out how to go...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi RedTangerine,

    Based on my understanding, I create the following sample table.

    sample table
    Then I create a 100% sttacked column chart similar with yours, please see the screenshot.



    Finally, create a measure using the formula to get the posted status vs total, and transfer the 100% Stacked Column Chart to Line Chart.

    posted-percentage = CALCULATE(SUM(Table3[value]),FILTER(Table3,Table3[status]="posted"))/SUM(Table3[value])


    If you want to calculate the percentage of unposted, just replace the posted to unposted in the filter of measure.

    Create a line chart, select the data as x-axis, the measure as value, you will get expected result.



    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi RedTangerine,

Based on my understanding, I create the following sample table.

sample table
Then I create a 100% sttacked column chart similar with yours, please see the screenshot.



Finally, create a measure using the formula to get the posted status vs total, and transfer the 100% Stacked Column Chart to Line Chart.

posted-percentage = CALCULATE(SUM(Table3[value]),FILTER(Table3,Table3[status]="posted"))/SUM(Table3[value])


If you want to calculate the percentage of unposted, just replace the posted to unposted in the filter of measure.

Create a line chart, select the data as x-axis, the measure as value, you will get expected result.



Best Regards,
Angelia

RedTangerine's avatar
RedTangerine
Frequent Visitor
8 years ago

Thank you.

This was exactly what I needed.

I changed it a little so that Count was used instead of sum and an extra calculate so that I could multiple by 100

but overall the solution was spot on:

posted-percentage = CALCULATE(CALCULATE(COUNT('riskman incident_fact'[Status (groups)]),FILTER('riskman incident_fact','riskman incident_fact'[Status (groups)]="posted"))/COUNT('riskman incident_fact'[Status (groups)])*100)