How to easily calculate a % in Power BI

[Anonymous]

Hi all,

Jst have a quick query, I am trying to calculate a % in power BI and having a small bit of trouble.

Example below; bascially trying to calculate % of queries responded to within 0,1,2,3 days etc

Example below of figures:

QUERIES:                               DAY

12                                          0

16                                          1

8                                            2

12                                          3

Would be grateful for any help, thanks a million!!!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

As what is suggested by @Jimmy801 , you may create a function which applys the specific transformations. And then you may input the corresponding table as parameter to get the result. You may create a blank query with the following m codes in 'Advanced Editor'. The pbix file is attached in the end.

TestFunction:

(tab as table)=>
let
    Source = tab,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Queries", Int64.Type}, {"Day", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Queries]/List.Sum(#"Changed Type"[Queries]),Percentage.Type)
in
    #"Added Custom"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft 

Thanks a million for this 🙂

[mahoneypat]

This can certainly be done in your query, but it is better done in DAX with a measure so you are not storing an extra column in your model and (if needed) the result can be responsive to slicers, etc.  For example, an expression like this would give you your % of total in a Table visual with your Day column and this measure (replacing Table with your actual table name.

Pct Total Queries = DIVIDE(SUM(Table[Queries]), CALCULATE(SUM(Table[Queries]), ALL(Table[Day])))

Regards,

Pat

[Anonymous]

Hi,

@mahoneypat 

Thanks a million for the below.

If I wanted to get the % of each individual query responded to within the individual days instead of total, will I have to alter the formula? 🙂

[mahoneypat]

You would have to adapt the expression.  The ALL(Table[Column]) in the calculate remove any filter from that column prior to doing the calculation.  Depending on where you use the expression (e.g., what columns are used for row/column headers, legend, axis, etc.), you will need to use similar ALL expressions (or the REMOVEFILTERS function which does the same as ALL in this case and may be more intuitive).

Regards,

Pat

[Jimmy801]

Hello @Anonymous 

you can add a new column and put a formula like this

[Queries]/List.Sum(ChangedType[Queries])

here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEyUIrVATLNgExDMNMCyDKCCILkjZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Queries = _t, Day = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Queries", Int64.Type}, {"Day", Int64.Type}}),
    AddPercentage = Table.AddColumn
    (
        ChangedType,
        "Percentage",
        each [Queries]/List.Sum(ChangedType[Queries]),
        Percentage.Type
    )
    
in
    AddPercentage

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 

Thanks a million for the below.

Is there a generic code I could use going forward to calcualte my % as my data will change every month?

Thanks a million 🙂