Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CC__123
Frequent Visitor

How to create a chart that shows a decrease over time without a date variable

Hi all,

 

I'm new to Power BI and I can't figure out how to create a chart (preferably an area or line chart) which shows the decrease in number of participants (in %) over a period of time, without using a date-type variable.

 

Ex1.png

1= active, 0 = not active

 

For example, I'm trying to create a chart which will show that in period 1 all participants are active (100%) but in period 2 only 83% of the participants remained active and in period 3 there's 67% left.  

 

I've seen these charts before but the X-axis is always a date-related variable, while in this instance the period-variable is unrelated to a specified date.

 

I hope someone can help me!

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

@CC__123 -

Unpivot your table using Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lHCxLE6cAVGWBQYICswRpGAYSQFJoRMMCXkBjMM48EmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, #"Period 1" = _t, #"Period 2" = _t, #"Period 3" = _t, #"Period 4" = _t, #"Period 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Period 1", Int64.Type}, {"Period 2", Int64.Type}, {"Period 3", Int64.Type}, {"Period 4", Int64.Type}, {"Period 5", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

then write a measure as:

Measure = DIVIDE(CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute])),COUNTROWS('Table'))

format as 'Percentage' > should return as:

cc_123.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
ChrisMendoza
Resident Rockstar
Resident Rockstar

@CC__123 -

Unpivot your table using Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lHCxLE6cAVGWBQYICswRpGAYSQFJoRMMCXkBjMM48EmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, #"Period 1" = _t, #"Period 2" = _t, #"Period 3" = _t, #"Period 4" = _t, #"Period 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Period 1", Int64.Type}, {"Period 2", Int64.Type}, {"Period 3", Int64.Type}, {"Period 4", Int64.Type}, {"Period 5", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

then write a measure as:

Measure = DIVIDE(CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Attribute])),COUNTROWS('Table'))

format as 'Percentage' > should return as:

cc_123.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



analyticsnags
Regular Visitor

I hope Power Bi by default order by X axis based on measure you use.

 

So any bar chart / column chart you can acieve it by default. If it is not working try to change the  sort property by clicking the three dots on top of chart.

Sort.JPG

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.