How to show percentage of count by different units?

[NicoleTy]

Hi,

I'm new to PowerBI need some help figure this out. I have two tables t1 and t2 generated by a sql query from table t0 group by t0.TimeDuration:

The schema for t0,t1, t2 is:

t0:

|TimeDuration|Contry|type|Category|

t1:

|TimeInHours | CountByHours |Country |Type | Category

  < 1hr                   10                   USA      T1       C1

  1-2-hr                  20                   USA      T2       C1

  2-4-hr                  10                   USA      T2       C1

t2:

|TimeInDays | CountByDays |Country |Type |Category

     <1 day               10              USA   T1           C1

     <1 day                30             USA   T2           C1

Now I need to have 2 pie charts with percentage of total number of each each time duration(by hour and by day) on the same report, use can see the result based on filter specified like country, type or category.

Is this even possible to do with powerbi? Do I need to define some measure or create table relathionship to accomplish that?

Thanks in advance.

-Nicole 

[Greg_Deckler]

In Power Query, use the "Split" functionality for t1 and t2 to split them on the | character (use Custom, |). You can import t1 into a table and then maybe append a second query to t2 or you could have them in separate tables and create measures that aggregate them.

[NicoleTy]

Hi Smoupre,

Can you give me an example of doing that?

-Nicole

[Greg_Deckler]

In Query Editor, choose your column, go to Transform and in the "Text Column" area of the ribbon, choose Split Column button, By Delimitor, Custom, |. Default should be all occurrences. The resulting "M" code will look something like this:

= Table.SplitColumn(#"Changed Type","ranges",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"ranges.1", "ranges.2", "ranges.3", "ranges.4", "ranges.5"})

You can do this for both t1 in one query and get rid of t2 in that query (remove other columns).

Repeat for t2, removing t1 from this query but right after "Source" step, click "Append Queries" from "Combine" area of ribbon on the Home tab.

[NicoleTy]

Unfortunately, that's not what I was asking.

I have the data already splitted and loaded correctly in PowerBI.

My question was how can I calculate the percentage of the count of hours in t1 and count of days in t2 filtered by different columns in one report.

Anyone can help?