Dax Calculation for Unit Price

DAX DAX Calculations
1

Hello EDNA Forum
Good Day!
I am using a calculated column in my data source to calculate the following:
Unit Price - Revenue/Usage Quality
and
% Quantity Used - Usage Quality/SOLI Quality

How do I consolidate transactions lines by the Sales Order ID to obtain the correct Unit Price and % Quality Used calculation?
The per line calculation in my report is incorrect, I need to consolidate the transaction lines by the sales order ID to get the correct calculation.
Attachment:
Thank you in advance for your time

Unit Price_ % Quantity Used.xlsx (11.9 KB)

2

@ambidextrousmentally,

The Power Query Group By function handles this perfectly:

image
image1236×85 9.16 KB

Here’s the setup:

image
image1048×686 17.2 KB

Once that runs, it’s straightforward to add two custom columns for unit price and usage percent. Here’s the full M code:

let
    Source = #"Original Data",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Order ID", Int64.Type}, {"SOLI Quanity", Int64.Type}, {"Transaction Year", Int64.Type}, {"Transaction Month", Int64.Type}, {"Usage Quantity ", Int64.Type}, {"Revenue Amount USD", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sales Order ID", "SOLI Quanity", "Transaction Year", "Transaction Month"}, {{"Total Usage Quantity", each List.Sum([#"Usage Quantity "]), type nullable number}, {"Total Revenue", each List.Sum([Revenue Amount USD]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Unit Price", each [Total Revenue] / [Total Usage Quantity]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Unit Price", Currency.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "% Usage", each [Total Usage Quantity] /[SOLI Quanity]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"% Usage", Percentage.Type}})
in
    #"Changed Type2"

I hope that’s helpful. Full solution file attached.

– Brian
eDNA Forum - Consolidated Rows Solution.pbix (24.3 KB)

2 Likes
3

Hi @ambidextrousmentally, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Brian
Much appreciated!
Thank you