Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with several columns. One of the columns is labeled RIR. It is a calculated column that i created to calculate a rate based on data that is located in other columns. I am using the Matrix Table and I need the total to reflect my calculated columns measure, not the sum, average, min, max count etc options that you are forced to use. How can I get total & subtotal rows that use my actual measure? The column that says Sun of RIR is a custom column made with the formula :
RIR = IFERROR('2017 Workbooks'[Total Rec Cases]*(200000/'2017 Workbooks'[Total Hours - Supervisory& Craft]),0.00)
I want this to used to calulate the subtotals and totals when the table is filtered, instaed of summing them. The individual lines in the matrix (circled in green) are calulating the RIR correctly. The Subtotals and totals hoghlighted in red are not. I need to top line to do the following:
- Sum the Hours (this works)
- Sum the Cases (this works)
- Calulate the RIR according to my formula: RIR = IFERROR('2017 Workbooks'[Total Rec Cases]*(200000/'2017 Workbooks'[Total Hours - Supervisory& Craft]),0.00) instead of Summing it.
I need Line 1 to read:
Total hours total rec cases RIR
362,507 3.0 1.66
The RIR is calculated : 3*(200,000/362507)= 1.66
I need it to complete this calculation at the subtotal and total for each group, instaed of showing 79.46 (the sum).
Thanks!
Stephanie
Solved! Go to Solution.
Hi, what happens if use a measure:
RIR = IFERROR ( SUM ( '2017 Workbooks'[Total Rec Cases] ) * DIVIDE ( 200000, SUM ( '2017 Workbooks'[Total Hours - Supervisory& Craft] ) ), 0.00 )
Let me know
Regards
Victor
Hi @ScORE
Have you considered using a calculated measure?
Something like
Calc as Measure = SUM('2017 Workbooks'[Total Rec Cases]) * (200000 / SUM('2017 Workbooks'[Total Hours - Supervisory& Craft]))
Hi, what happens if use a measure:
RIR = IFERROR ( SUM ( '2017 Workbooks'[Total Rec Cases] ) * DIVIDE ( 200000, SUM ( '2017 Workbooks'[Total Hours - Supervisory& Craft] ) ), 0.00 )
Let me know
Regards
Victor
That worked! THank you. I tried that, but I was getting an werror because I was utting the sum in the wrong place!
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |