DAX Calculation for Collections %

[mlz]

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 see Charge Dates across the top and Collection Dates along the left side.

For each Collection Date, I need to calulate Collection %. For example, the calculation for Collection Dates Jan, Feb and Mar should be:

                    Jan 2016                Feb 2016

Jan           11351 / 17171       

Feb           4213 / 17171         12098 / 18218

Mar           1032 / 17171          4801 / 18218

I am new to DAX and Tabular.  Played with FIRSTDATE() function but no luck.  Any help is greatly appreciated.

[Phil_Seamark · Accepted solution]

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 · Accepted solution]

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?

[Phil_Seamark · Accepted solution]

HI @mlz

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

[mlz]

Collections is a fact table and slices by Collection Date and Charge Date.

Charges is also a fact table and slices by Charge Date only.

Basically, for Charges (Revenue) incurred in Jan 2016, I will collect throughout the year(s) and need to calculate the % collected each month.  Then, the same logic applies for Revenue incurred in Feb 2016.  Please keep in mind that I am using a date hierarchy so it will not always be aggregated by months.  The calculation needs to handle whatever level they are in the date hierarchy and whatever time frame they have chosen.

Thank you so much!

[MarkLaf · Accepted solution]

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?

[mlz]

A little closer. Below is the calculation I ended up with based on what your calculation.  The problem is that it is dividing each Collection by each Charge amount (directly accross) and I only want to divide by 17171.

I tried to build my calcualtion like yours but ended with nothing showing in Collections % or an error on the calculation. So I ended up with this.

Collection %:=Divide([Net Collections],[Net Production],0)

 

Below is my bus matrix. The only relationship between collection and charges is Charge Date (Visit Date). 

I can't tie the collection directly to the charge because some collections don't tie to a charge.  In the Collection % I need to reflect all Collections.

[v-xjiin-msft · Accepted solution]

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]

I want to thank all that replied to my post.  Just by having you request more clarification about the data and the relationships, I was able to tweek my data model that produced the numbers I was looking for.  The trick was setting up the relationships correctly.  Someone else mentioned that early on.  I thought I had it correctly initially but as I kept digging I found that I had to fine tune it.

Thanks again. 

[mlz]

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

 

[mlz]

• I have a Tabular Cube and need to calculate Collections %  (Collections / Charges)

  BUT the Charges to use is the one highlighted in yellow (below).

  In the picture, Charge Dates are across the top and Collection Dates along the side.

  For each Collection Date, I need to calculate Collection %.   So, for example, for Collection Dates Jan, Feb and Mar (along side), my calculation for each Service Date (Jan 2016 and Feb 2016 at the top) should be:

                               Jan 2016                      Feb 2016

• Jan 2016    11351 / 17171          

  Feb 2016    4213 / 17171                 12098 / 18218

  Mar 2016    1032 / 17171                 4801 / 18218

  I am new to Tabular and DAX.  I think the function I need is FIRSTDATE() but with a condition where FIRSTDATE is the one where collection date = charge date.  Any help is greatly appreciated.