Forum Discussion

mlz's avatar
mlz
New Member
8 years ago
Solved

DAX Calculation for Collections %

I have a Tabular cube and need to calculate Collections %   (Collections / Charges) BUT the denominator (Charges) to use should be the one highlighted in yellow (below). In the picture, you will se...
Tips and Tricks
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI mlz

     

    Are you able to describe the tables that Collections and Charges come from?  This will help clarify what DAX might be needed.

  • MarkLaf's avatar
    MarkLaf
    8 years ago

    So each collection has a related charge (i.e. many/one relationship between collections/charges)? If that is true, make sure a relationship is set up through the modeling tab, and then try the following measure:

    % of Charge = CALCULATE(DIVIDE(SUM(Collections[Amount]),SUM(Charges[Amount])),Collections)

    Is that getting closer to what you are looking for?

  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi mlz,

     

    Could you please share us some sample source data which we can copy and paste directly of the two tables if possible? So that we can make some proper tests. 

     

    Also, if you can share us your pbix file with One Drive or Dropbox or something else. It will be more helpful for us to find a solution.

     

    Thanks,
    Xi Jin.

v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

Hi mlz,

 

Could you please share us some sample source data which we can copy and paste directly of the two tables if possible? So that we can make some proper tests. 

 

Also, if you can share us your pbix file with One Drive or Dropbox or something else. It will be more helpful for us to find a solution.

 

Thanks,
Xi Jin.

mlz's avatar
mlz
New Member
8 years ago

I am working from a Tabular cube and not PowerBI.  As far as giving a data sample, I decided to just mock up the data shown in the pictures. Hope that helps clarify.  I created temp tables that resemble my facts and the final query that gets me what I need.

In the picture below, column ch2_ChargeAmount is what does the trick.  It remains constant throughout the Collection dates.  This is what I need to accomplish in the Tabular cube. 

 

drop table if exists #Charges
drop table if exists #Collections

 

select 201601 as ChargeDateKey, 17170 as ChargeAmount into #Charges
union select 201602, 18217
union select 201603, 10268

 

select 201601 as CollectionDateKey, 201601 ChargeDateKey, 11351 as CollectionAmount into #Collections
union select 201602, 201601, 4213
union select 201603, 201601, 1032

 

select
 c.CollectionDateKey
 ,c.ChargeDateKey as Collection_ChargeDateKey
 ,c.CollectionAmount
 ,ch.ChargeDateKey as Charge_ChargeDateKey
 ,ch.ChargeAmount
 ,ch2.ChargeAmount as ch2_ChargeAmount
 ,FORMAT(CollectionAmount * 1. / ch2.ChargeAmount,'P2') as CollectionPct
from #Collections c
 --charges to collections (usually 1:M) but not all collections are tied to a charge so need a left join to account for all collections
left join #Charges ch on c.CollectionDateKey = ch.ChargeDateKey
left join #Charges ch2 on c.ChargeDateKey = ch2.ChargeDateKey