Forum Discussion
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 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.
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.
8 Replies
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.
HI mlz
Are you able to describe the tables that Collections and Charges come from? This will help clarify what DAX might be needed.
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!
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?
