Forum Discussion

ch_7's avatar
ch_7
Regular Visitor
3 years ago
Solved

apply calculated percent to multiple rows

Hello, I have source data with a unique order # that applies to two different unique site #'s, with each site receiving a delivered volume. 

 

A separate source provides the line item charges associated with the order #. However, it applies the charges to only one site. The charges are broken out one row per line item.

 

In pbi I have relationships set-up and can show the line details and charges, but they show for each site, essentially doubling the amounts.

 

I have a calculated field that provides the % of volume delivered to each site and would like to apply that same % to each line item according to the order # and site #. The % is calculated by dividing the sum of volume per site by the sum of total ordered volume. 

 

I cannot figure this out - any help is much appreciated!

 

site #order #order_typevolumeline_detailsline_net_charge
22441140Split5400delivery charge1.23
22441140Split product charge38.4
22441140Split product 2 charge46.08
22441140Split MINIMUM GUARANTEE107.52
22441140Split SOC-STOP OFF CHARGE119.52
22441140Split product 3 charge122.88
55221140Split2800delivery charge1.23
55221140Split product charge38.4
55221140Split product 2 charge46.08
55221140Split MINIMUM GUARANTEE107.52
55221140Split SOC-STOP OFF CHARGE119.52
55221140Split product 3 charge122.88
      
    Total net charge of order435.63
      
      
site #order #order_type volume% split
22441140Split 540066%
55221140Split 280034%
Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi ch_7 ,

     

    Try this:

    %Split = 
VAR __VOLUME =
    CALCULATE (
        SUM ( 'Table1'[volume] ),
        FILTER ( Table1, Table1[site #] = EARLIER ( Table1[site #] ) )
    ) //volume per site
VAR __TOTAL_VOLUMME =
    SUM ( Table1[volume] )
RETURN
    DIVIDE ( __VOLUME, __TOTAL_VOLUMME )

     

1 Reply

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    3 years ago

    Hi ch_7 ,

     

    Try this:

    %Split = 
VAR __VOLUME =
    CALCULATE (
        SUM ( 'Table1'[volume] ),
        FILTER ( Table1, Table1[site #] = EARLIER ( Table1[site #] ) )
    ) //volume per site
VAR __TOTAL_VOLUMME =
    SUM ( Table1[volume] )
RETURN
    DIVIDE ( __VOLUME, __TOTAL_VOLUMME )