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.
Creating subtotals in Waterfall chart
Hi, I am trying to create a waterfall chart in PowerBI that looks something like this: However, I do not know how to assign specific categories within my dataset to act as "totals" or subtotals". I would appreciate someones advice on how to set-up my dataset and visual in Excel and powerBI, respectivelly. Thank you very much in advance, Kind regards.
Subtotals by Data Group
This is ny first day with Power BI. I am trying to create a band that will give a subtotal of product prices for each category. I find the Table Wizard to be unsuitable for what I am doing. i.e. I want the products listed by category then a subtotal of the prices for each category. I have created a table that will show the columns of interest but I can see how to do the Category Subtotals.
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=trueRelative Positioning on Shapes Changing Based on Resolution
I'm trying to create a sort of dynamic PNL using the basic matrix visualization. The issue is that I want subtotals for each of the bolded catergories, and then a grand total (Net Income). I've created a matrix that functions properly, but there are extra lines of data that repeat catergory sums. In order to negate this issue, I placed grey shapes over the unwanted rows to hide them. The issue is that when I view the report on screens of different resolutions, the shapes move slightly. This movement will cause the shapes to be covering the wrong rows of data. Is there any way to either A) lock the shapes in place relative to the matrix or B) insert custom subtotal rows in a matrix? Attached is a screenshot of the first column of this matrix, with the shapes in place below "Total Utilities" and "Taxes & Insurance"
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%
