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
Solution 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 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.