Total/Subtotal value sum of lines measure result
Hi, I'm trying to create a measure with a specific behaviour. Both cycles and time are a simple SUM, nothing else. Cycles (#/h) measure is define as seen below. Cycles (#/h) = DIVIDE([Cycles (#)],[Time (h)]) What I want a result is: - If I have more than one Line selected (as the image) the total or subtotal should be 144.17 + 83.46 = 227.63 - If I have only one Line selected the total or subtotal should be either 144.17 or 83.46, depending on which is selected. How can I achieve this? Thank you.
Divide all categories by one category-total
Hello all, If anyone can help me it is much appreciated. I have a financial report were I divide the differend revenues by the total revenue. This is working. But now I like to divide the costs also by the total revenue. I first made a formula for the total Revenue. Kosten%4 = CALCULATE(SUM(GeneralLedgerBalances[AmountDC]), 'Volgorde'[Rapportage omschrijving] = "Omzet verpakken", ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr])) (I added 'Rapportage_Grootboek'[reknr]), because 'Rapportage_Grootboek'[oms25_0] is sorted by Rapportage_Grootboek'[reknr])) After this I first tested if I got the right percentages of the revenue, and this was working. Kosten%1 = VAR Omzetverpakken = CALCULATE(SUM(GeneralLedgerBalances[AmountDC]), 'Volgorde'[Rapportage omschrijving] = "Omzet verpakken", ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr])) RETURN DIVIDE( CALCULATE(SUM(GeneralLedgerBalances[AmountDC]), ALL(Volgorde[Rapportage omschrijving]), ('Volgorde'[Rapportage omschrijving]) = "Omzet verpakken") , Omzetverpakken ,0) Now I thought it was simple to get all the percentages by replacing the "Numerator" in: Kosten%2 = VAR Omzetverpakken = CALCULATE(SUM('GeneralLedgerBalances'[AmountDC]), 'Volgorde'[Rapportage omschrijving] = "Omzet verpakken", ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr])) RETURN Divide( SUM('GeneralLedgerBalances'[AmountDC]), Omzetverpakken, 0) For the revenue it is still working, but for the other categories not. I get only zero's because of the alternate result. So, I deleted the Divide functions and made the following formula: Kosten%7 = VAR Omzetverpakken = CALCULATE(SUM('GeneralLedgerBalances'[AmountDC]), 'Volgorde'[Rapportage omschrijving] = "Omzet verpakken", ALLSELECTED('Rapportage_Grootboek'[oms25_0], 'Rapportage_Grootboek'[reknr]), VALUES(Volgorde[Rapportage omschrijving]) ) RETURN SUM('GeneralLedgerBalances'[AmountDC]) / Omzetverpakken For the revenue this is still working, but now I get a message for the other categories. See the results below. By the way, the percentage of the Total 0,23 is shown and is correct. I have search a lot of time to solve this problem, but I overlook something. And I hoop that one of you can help me with this. Thank you in advance!
Table subtotal is wrong
I have a matrix that shows the incident count for the user-selected month(s) and the previous equal number of months. Its rows are broken down by Week of the Month (as defined below*) and day and includes a grand total. The problem is the Week of the Month subtotals are wrong if multiple months are selected. However, the daily values and the grand total compute correctly. Details: I have two calculations (_Inc Count and _Inc Count PMs) which count the number of incidents for the selected months and for the equivalent number of previous months, respectively. So if I select April and March, _Inc Count PMs returns the counts for Jan and Feb. This works great at the day level and at the overall level. But for some reason, at the weekly level, it's very wrong. As shown here, the daily PMs values (yellow highlight) are correct and the final total of 67,177 (also in yellow) is correct but the W1 days clearly do not add up to 41,722. Here is the view collapsed: I can't figure out why the weekly values do not equal the total of the daily values * We use a 4-week month where the 4th week of fhe month may have more than 7 days. Here is the formula for the week number: __Date Scorecard Week of Month (#) = IF('DATE Table'[Day] >= 1 && 'DATE Table'[Day] <= 7, "W1", IF('DATE Table'[Day] >= 8 && 'DATE Table'[Day] <= 14, "W2", IF('DATE Table'[Day] >= 15 && 'DATE Table'[Day] <= 21, "W3", IF('DATE Table'[Day] >= 22, "W4")))) The calulcation for _Inc Count = DISTINCTCOUNT('Incidents'[Inc_Number]) Here is a link to the how I calculate the PMs value: PMs Calculation So why is the matrix subtotalling incorrectly???? Please help!ASKING HELP: SUM OF SUBTOTALS
Thanks in advance for those that will help. I want to get the sum of subtotal based on the Slicer that i use. For example we have several teams for each country. I want to get the total for that country so when i put it on the table i can get the % contribution for each team towards the country total. Slicer depends on the country. SAMPLE DATA SAMPLE OUTCOME DESIRED sample data source: https://docs.google.com/spreadsheets/d/1bYFmo2TIHc8GX2ze889sQ0qcCPyqMQr_/edit?usp=sharing&ouid=107497640201654826090&rtpof=true&sd=true
Percentage over subtotal in hierarchical matrix
Hi Everyone, I have the following hierarchical matrix with all my rows expanded. I want to calculate the % based on the respective total for In/Out of that account as you will see on the example below. As you can see for the first row I have an amount of 120, which if divided by Total In for that account will be 120/450 = 26.6%. The same goes with the subtotal for Type. They should also be calcualted based on the total for In/Out (ex: 370/450 = 82.2%) In terms of the data, all fields in this matrix come from a single table called Transactions. Here is an example to guide you. My matrix obviously has several accounts, so I want the percentage calculated based on the totals for each account. Any suggestions on how to write a measure that could calculate what I want would be appreciated. Thank you Name Account In/Out Type Counterparty Country Amount Volume % A 123 In Wires Mario USA 120 5 26.6% - - - Wires Jason JAPAN 250 10 55.5% - - - Total Wires - - 370 15 82.2% - - - Cash Sarah USA 45 2 10% - - - Cash Mario MEXICO 35 1 7.7% - - - Total Cash - - 80 3 17.7% - - Total In - - - 450 18 100% - - Out Wires Kyle CANADA 400 8 42.1% - - - Wires Sarah BOLIVIA 350 2 36.8% - - - Total Wires - - 750 10 78.9% - - - Checks Camila USA 200 5 21.1% - - - Total Checks - - 200 5 21.1% - - Total Out - - - 950 15 100%Subtraction of percentages of subtotals in hierarchy
Hi All I am a management student and in my final year project I am trying to achieve a particular solution to a complex problem. Here is a screen recording of my "Problem Statement" Here is a link to the sample data and formula Any suggestion and comment will help me a lot Thanks in advance nandukrishnavs
Matrix weighted averages in subtotals and total
Hi, Is there someone who can help me by the following. I have the folowing table: Table: Salesman Region Salesman Sales Budget Margin Index Margin Budget A A1 1.000.000,00 50 500.000,00 A A2 2.000.000,00 60 1.200.000,00 A A3 2.500.000,00 55 1.375.000,00 B B1 2.000.000,00 60 1.200.000,00 B B2 4.000.000,00 45 1.800.000,00 B B3 3.000.000,00 55 1.650.000,00 C C1 1.500.000,00 55 825.000,00 C C2 2.000.000,00 50 1.000.000,00 C C3 6.000.000,00 40 2.400.000,00 Total 24.000.000,00 11.950.000,00 Region A AVG Margin Index 55,91 Region B AVG Margin Index 51,67 Region C AVG Margin Index 44,47 Total AVG Margin Index 49,79 And I want to put it in a matrix like the one below: Subtotals by region and a grand total 1) Sales Budget is a SUM 2) Margin Budget is a Sum 3) Margin Index is a weighted AVERAGE (this is the one I am fighting with !!!!) Requested Visual Power BI Matrix Region Salesman Sales Budget Margin Index Margin Budget A 5.500.000,00 55,91 3.075.000,00 A1 1.000.000,00 50,00 500.000,00 A2 2.000.000,00 60,00 1.200.000,00 A3 2.500.000,00 55,00 1.375.000,00 B 9.000.000,00 51,67 4.650.000,00 B1 2.000.000,00 60,00 1.200.000,00 B2 4.000.000,00 45,00 1.800.000,00 B3 3.000.000,00 55,00 1.650.000,00 C 9.500.000,00 44,47 4.225.000,00 C1 1.500.000,00 55,00 825.000,00 C2 2.000.000,00 50,00 1.000.000,00 C3 6.000.000,00 40,00 2.400.000,00 Total 24.000.000,00 49,79 11.950.000,00
