CODEWITHSUNDEEP
powerbipowerbi-desktop
Kamal Panhwar
Kamal Panhwar

Reputation: 2399

Power Bi calculating row and getting ratio

I am complete new to power bi, I am trying to get ratio for my tables. following is table

user_id status attempts
1 ok done
1 fail done
2 ok done
2 ok done
2 fail done

Now I have to show each user how many attempts he did and how many were fails and how many were ok.

I need to show following results | user_id | ratio| |---------|-----| | 1 | 60% | | 2 | 40% | The calculation is coming from totals rows and divided by first user attempted then I have to further by ok and failure. I tried many ways by created new table of counting and then group by but unfortunately id did not work.

Upvotes: 1

Views: 1182

Answers (3)

Marcus
Marcus

Reputation: 3995

You can easily do this using DAX with your current data model. There is no need to add additional tables with different grouping and such - you only need a few measures added to your model. For simplicity, here are three measures:

One general measure to count attempts in the current filter context:

# attempts = 
COUNTROWS ( 'Table' )

A second to calculate the number of attempts in the current filter context vs. total attempts:

% of attempts = 
VAR _total = CALCULATE ( [# attempts] , ALL ( 'Table' ) )
RETURN
DIVIDE ( [# attempts] , _total )

And a third to calculate the ratio of successful attempts to all attempts, in the current filter context:

success ratio = 
VAR _oks = CALCULATE ( [# attempts] , 'Table'[status] = "ok" )
RETURN
DIVIDE ( _oks , [# attempts] )

Apply the correct formatting in Measure Tools and put user_id together with the three measures (# attempts, % of attempts and success ratio) into a new table visualisation:

enter image description here

Upvotes: 2

Kemal Kaplan
Kemal Kaplan

Reputation: 1024

By using M;

let
    Source = Excel.Workbook(File.Contents("C:\Users\kkaplan\Desktop\a.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ok", each 100*Table.RowCount(Table.SelectRows(_, each [Column2] = "ok"))/Table.RowCount(_) , type nullable number},
    {"fail", each 100*Table.RowCount(Table.SelectRows(_, each [Column2] = "fail"))/Table.RowCount(_) , type nullable number}}),
    #"Rounded Off" = Table.TransformColumns(#"Grouped Rows",{{"ok", each Number.Round(_, 2), type number}}),
    #"Rounded Off1" = Table.TransformColumns(#"Rounded Off",{{"fail", each Number.Round(_, 2), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Rounded Off1",{{"Column1", "user"}})
in
    #"Renamed Columns"

Where data is like

And output is like;

enter image description here

Upvotes: 2

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Please use Group By advance option to group by the user_id and status and count the rows.

enter image description here

Here Hospital_id should be user_id and name should be status.

After that add another group by just to get the total attempts per user.

enter image description here Just grouping with user_id and for aggregation add all rows and expand the table. This will give you the number of attempts and right and wrong count. Add another calculated field to do the division and you are good to go. You can do this calculation using a measure too since you are new I will suggest you this way.

Upvotes: 1

Related Questions