Forum Discussion
DAX Calculation for Collections %
HI mlz
Are you able to describe the tables that Collections and Charges come from? This will help clarify what DAX might be needed.
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?
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.
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?
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.
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.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 #Collectionsselect 201601 as ChargeDateKey, 17170 as ChargeAmount into #Charges
union select 201602, 18217
union select 201603, 10268select 201601 as CollectionDateKey, 201601 ChargeDateKey, 11351 as CollectionAmount into #Collections
union select 201602, 201601, 4213
union select 201603, 201601, 1032select
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.ChargeDateKeyI 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.