Power BI - How to calculate a division without using DAX

Question

This is a short example of the development created for a specific requirement. The idea is to have only one measure that provides all the results for the crucial metrics. Everything has been working as expected. (Measure = SUM('Values'[Value])).

However, the client is requesting a new metric that involves a division between two previous results, but with a specific condition: It is not allowed two use DAX for this calculation. The goal is to have this result also contained on the unique metric used until now.

I have been searching for a possible solution, but so far, anything seems to be useful for this purpose. I haven't found a way to calculate this division of calculated results using Power Query or implementing another alternative.

Is there a way to get this result to include it in the global measure?

Chris Chris · Accepted Answer · 2020-05-12T14:05:06

I'm not sure that I understood the question 100%, but you can add a new row in the value table in PowerQuery with the result of the division of

sum(ID2) / sum(ID1)

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Value"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value", Int64.Type}, {"Components", type text}}),

    Value5 = List.Sum(Table.SelectRows(ChangeType, each [ID] = 2)[Value]) / List.Sum(Table.SelectRows(ChangeType, each [ID] = 1)[Value]), 
    Calculated_Measure5 = Table.Combine({ChangeType, Table.FromRecords({[ID = 5, Value = Number.From(Value5), Components = "ID 2 / ID 3"]})})

in
    Calculated_Measure5

Best regards Chris

Power BI - How to calculate a division without using DAX

1 Answers