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.

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
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.

  • mlz's avatar
    mlz
    New Member
    8 years ago

    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's avatar
      MarkLaf
      Icon for Super User rankSuper User
      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?

      • mlz's avatar
        mlz
        New Member
        8 years ago

        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.