Forum Discussion

HPax's avatar
HPax
Frequent Visitor
1 year ago
Solved

Display column total on measure value

Hi all, I am new to Power BI and can't seem to display the totals on my matrix for each column. Hoping someone can help! 

 

My matrix has (3) columns. The 1st is a quick measure, the 2nd is a custom measure that counts rows from my data source, and the 3rd is a custom calculation. My 1st column shows the totals, however the 2nd and 3rd do not. After doing some research, I believe the issue is due to custom forumlas in the 2nd and 3rd columns. But I've tried many different recommendations from other thread with 0 success. 

 

My formulas for the 2nd and 3rd columns are as follows: 

 

Count of Completed Training = COUNTROWS(FILTER('Training Tracking', SELECTEDVALUE('Training Tracking'[Training Topic - Levels]) = 'Training Tracking'[Training Topic - Levels] && SELECTEDVALUE('Current Employee Names'[Employee Name]) = 'Training Tracking'[Trainee]))
 
% Complete Training Level = IF([Count of Completed Training]> 1, [Required Training]/1, if(NOT ISBLANK([Count of Completed Training]), [Count of Completed Training]/[Required Training]))
 
Because the "Count of Completed Training" formula counts duplicates (intended). I had to create an IF statement so the "% Complete" didnt exceed 100%. 

 

I am not overly concered about the total on "Count of Completed Training", but the "% Complete" is key for the functionality of my report. 

 

Also, I've turned the column and row subtotals to "on" in the visulazations, but the apply settings to the per column level is set to "off" and wont allow me to change it (greyed out). 

 

Let me know if you require anymore info. 

 

Thanks in advance!! 

 

 

"Calculated Column"
CUSTOMCOLUMN
sub totals
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi HPax ,

     

    Have you tried SUMX?

    Count of Completed Training = SUMX('Training Tracking',CALCULATE(COUNTROWS(FILTER('Training Tracking', SELECTEDVALUE('Training Tracking'[Training Topic - Levels]) = 'Training Tracking'[Training Topic - Levels] && SELECTEDVALUE('Current Employee Names'[Employee Name]) = 'Training Tracking'[Trainee]))))

    If the above DAX does not work, please provide sample data or a pbix file(exclude sensitive data).

     

     

     

     

    ——————————————————————————————————————————————————

    If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

     

    Best regards,

    Mengmeng Li

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi HPax ,

     

    Have you tried SUMX?

    Count of Completed Training = SUMX('Training Tracking',CALCULATE(COUNTROWS(FILTER('Training Tracking', SELECTEDVALUE('Training Tracking'[Training Topic - Levels]) = 'Training Tracking'[Training Topic - Levels] && SELECTEDVALUE('Current Employee Names'[Employee Name]) = 'Training Tracking'[Trainee]))))

    If the above DAX does not work, please provide sample data or a pbix file(exclude sensitive data).

     

     

     

     

    ——————————————————————————————————————————————————

    If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

     

    Best regards,

    Mengmeng Li

     

     

    • HPax's avatar
      HPax
      Frequent Visitor
      1 year ago

      That worked! Thank you so much! I had tried Sumx previously, but nested the formulas incorrectly.

       

      But this does create a new issue for me. Since the column is counting duplicates, the % complete totals are artificially inflated. I was able to solve this on my own using the following. 

       

      Created a column using the below: 

       

      Unique/Repeat = RANKX(FILTER('Training Tracking', 'Training Tracking'[Trainee] = EARLIER('Training Tracking'[Trainee]) && 'Training Tracking'[Training Topic - Levels] = EARLIER('Training Tracking'[Training Topic - Levels])), [Date],, ASC, Dense)

       
      Then added this condition onto my previous formula.
       
      Count of Completed Training = SUMX('Training Matrix', CALCULATE(COUNTROWS(FILTER('Training Tracking', SELECTEDVALUE('Training Tracking'[Training Topic - Levels]) = 'Training Tracking'[Training Topic - Levels] && SELECTEDVALUE('Current Employee Names'[Employee Name]) = 'Training Tracking'[Trainee] && 'Training Tracking'[Unique/Repeat] = 1))))

       

      Thanks again!