Cumulative Sum BY CLIENT and without date or index column
Hello, dear comunity: I'm here cause need help with doing a cumulative sum of sales wich I calculated it with SUM(), need to be representated on a pivot table in excel or matrix in Power BI with DAX. If I do a sum of sales and bring customername to labels I have this result, and I need a solution to receive the orange column's behavior. the sales must be ordered by "Total sales in period" value. I got the sample data in this url to practice: Sample Sales Data (kaggle.com) Sorry for my bad grammar in english, I'm still learning. Kind regards.
Cumulative Total Percentage and Categorization by Layer
Hello, I'm trying to categorize the cumulative percentage of different clients revenue example clients | Cumulative percentage % | Layer Client 1 | 10% | Layer 50 Client 2 | 23% | Layer 50 Client 4 | 42% | Layer 50 Client 5 | 55% | Layer 50 Client 6 | 61% | Layer 80 . . . . Client X | 100% | My issue is that if I where to make a condition to throw Layer 50 to those =< than 50% then it won't take in account those Clients that make it reach 50% like in the example Client 4 was at 42% and client 5 made it reach 50% but went overboard by 5% but still is considered Layer 50% because it was the one who made it reach that value How can I make this work? TotCA= is basically Sum(Sales)
Adding Target Line for 2023 Cumulative Costs in Power BI Visual
Hello Power BI Community, I am seeking assistance with a visual I've created to compare cumulative costs for the years 2022 and 2023. For the visual, I have developed the following measure for cumulative costs: ```DAX Cumulative Costs = CALCULATE( SUM('Cost 1'[Amount EUR]), FILTER( ALL('Calendar'[Date]), // Remove the filter context from the Calendar table YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])) && MONTH('Calendar'[Date]) <= MONTH(MAX('Cost 1'[Date])) ) ) ``` This measure is placed on the Y-axis, with the Calendar Month on the X-axis, and the Calendar Year as the legend. My query is about incorporating a target line for 2023 cumulative costs into this visual. Currently, the use of the legend for differentiating between the years is preventing me from adding a secondary Y-axis. Is there a way to work around this issue? Should I create a separate measure to facilitate the addition of the target line? I have attached a sample of the data and a screenshot of the visual for your reference. Any guidance you can provide would be greatly appreciated. Sample Visual and sample Dataset Attached: https://drive.google.com/drive/folders/1IOviSSW_LKRWuyKH_R7Gh6RaDDj6_zZV?usp=sharing Best Regards, Manish Tripathi
Cumulative function based on a date range filter
Hi everyone, I'm pretty new to using DAX function in Power BI and I'm having a hard time trying to create a cumulative function based on certain requirements. Please, someone help or maybe give some ideas on how I could modify the formula so that it does what I want. Any help would be appreciated. I have one date range filter in the report page and based on this I want to calculate the cumulative function in the following way: - for the first month in the range to have the result obtained from 3 measures, like this: measure1-measure2+measure3 - and for the next months, until the last date in the date range filter, to take the result from the previous month + measure1- measure2. The date filter is taken form a DateTable which is linked with the tables where the measures are. The formula that I have right now is this : CumulativeResult = VAR StartDate = MIN('datatable'[Date]) VAR EndDate = MAX('datatable'[Date]) VAR SelectedRange = FILTER('datatable', 'datatable'[Date] >= StartDate && 'datatable'[Date] <= EndDate) RETURN SUMX( SelectedRange, IF( 'datatable'[Date] = StartDate, [measure1] - [measure2] + [measure3], CALCULATE( [measure1] - [measure2], FILTER( ALL('datatable'), 'datatable'[Date] = EARLIER('datatable'[Date]) ) ) ) ) Thanks!
How can I create a montly comulatived graph
Hi. I would appreciate some help with a BI problem of mine. I would like to create a Graph like this I have one table with budget values Cost center Accont Month Budget 11220 Logistic 36900 services sold Jan 778 11222 Logistic 62100 Rent IT-appl Jan 85 11223 Logistic 64500 Office appliances Jan -404 11220 Logistic 36900 services sold Feb 778 11223 Logistic 62101 Rent IT-appl Feb 85 11224 Logistic 64501 Office appliances Feb -404 11220 Logistic 36900 services sold Mar 778 11224 Logistic 62102 Rent IT-appl Mar 85 11225 Logistic 64502 Office appliances Mar -404 11220 Logistic 36900 services sold Apr 778 11225 Logistic 62103 Rent IT-appl Apr 85 11226 Logistic 64503 Office appliances Apr -414 11220 Logistic 36900 services sold May 778 11226 Logistic 62104 Rent IT-appl May 85 11227 Logistic 64504 Office appliances May -414 11220 Logistic 36900 services sold Jun 500 11227 Logistic 62105 Rent IT-appl Jun 85 11228 Logistic 64505 Office appliances Jun -414 11220 Logistic 36900 services sold Jul 500 11228 Logistic 62106 Rent IT-appl Jul 85 11229 Logistic 64506 Office appliances Jul -414 11220 Logistic 36900 services sold Aug 778 11229 Logistic 62107 Rent IT-appl Aug 85 11230 Logistic 64507 Office appliances Aug -414 11220 Logistic 36900 services sold Sep 778 11230 Logistic 62108 Rent IT-appl Sep 85 11231 Logistic 64508 Office appliances Sep -414 11220 Logistic 36900 services sold Oct 778 11231 Logistic 62109 Rent IT-appl Oct 85 11232 Logistic 64509 Office appliances Oct -414 11220 Logistic 36900 services sold Nov 778 11232 Logistic 62110 Rent IT-appl Nov 85 11233 Logistic 64510 Office appliances Nov -414 11220 Logistic 36900 services sold Dec 778 11233 Logistic 62111 Rent IT-appl Dec 85 11234 Logistic 64511 Office appliances Dec -414 And another table with results values Cost center Accont Month Result 11220 Logistic 36910 services sold Jan 397 11200 Logistic 62100 Rent Jan 500 11223 Logistic 64500 Office appliances Jan -200 11220 Logistic 36900 services sold Feb 900 11223 Logistic 62101 Rent IT-appl Feb 76 11224 Logistic 64501 Office appliances Feb -404 11220 Logistic 36900 services sold Mar 300 11224 Logistic 62102 Rent IT-appl Mar 20 11225 Logistic 64502 Office appliances Mar -300 11220 Logistic 35200 services sold Apr 600 11225 Logistic 62103 Rent IT-appl Apr 10 11226 Logistic 64503 Office appliances Apr -500 11220 Logistic 36900 services sold May 400 11226 Logistic 62104 Rent IT-appl May 50 11227 Logistic 64504 Office appliances May -800 11220 Logistic 36900 services sold Jun 600 11227 Logistic 62105 Rent IT-appl Jun 78 11228 Logistic 64505 Office appliances Jun -100 11220 Logistic 36900 services sold Jul 345 11228 Logistic 62106 Rent IT-appl Jul 97 11229 Logistic 64506 Office appliances Jul -200 11220 Logistic 36910 services sold Aug 778 11229 Logistic 62107 Rent IT-appl Aug 85 11230 Logistic 64507 Office appliances Aug -414 11220 Logistic 36900 services sold Sep 232 11230 Logistic 62108 Rent IT-appl Sep 43 11231 Logistic 64508 Office appliances Sep -400 11220 Logistic 36900 services sold Oct 434 11231 Logistic 62109 Rent IT-appl Oct 24 11232 Logistic 64509 Office appliances Oct -500 11220 Logistic 36900 services sold Nov 900 11232 Logistic 62110 Rent IT-appl Nov 67 11233 Logistic 64510 Office appliances Nov -300 11220 Logistic 36900 services sold Dec 879 11233 Logistic 62110 Rent IT Dec 8 11234 Logistic 64512 appliances Dec -398 I would like them to cumulate the values like this and I would like to be able to create the graph shown at the top. Cumulated Value Budget Result Jan 459 697 Feb 918 1269 Mar 1377 1289 Apr 1826 1399 May 2275 1049 Jun 2446 1627 Jul 2617 1869 Aug 3066 2318 Sep 3515 2193 Oct 3964 2151 Nov 4413 2818 Dec 4862 3307
Cumulative balance per customer
Hello, I have a table inclunding more then 10K rows and includinf the following columns: - customer (more then 200 customers) - invoice amount - invoice date - expected pay date of the invoice I want to calculate the cumulative balance of every customer over time, based on the invoice date en expected payment date of the invoice, so I can measure the exposure over time per customer. therefore, I created a Calender table for 3 years to measure the Exposure per customer. I was able to include 3 columns in this Calender table: - total invoice amount per date - total expected payment per date - total Outstanding (which is the total invoice amount - expected payment per date) Now I want to make a measure which calculatea the cumulative amount and make it possible to filter per customer. However, it's not possible for me to create a measure to calculate the cumulative measure per customer. Anyone who can help me? Is it because I've two different tables (1 with invoices per customer and 1 Calender table)? thank you in advance
