Calculating Commission on Sales
My objective is to present a report which shows each agent’s name, the total sales amount for that agent for the month and the commission earned based on the commission policy. Here is more context for you: Based on the document, the Commission Policy tells you how an agent's commission is calculated. To calculate an agent's commission you need to know what the agent's monthly salary is and how much sales they made for the month for the different services (application hosting, web services, infrastructure hosting). Once you have the agent's monthly salary and total monthly sales generated, you need to calculate the Salary Cover = Total monthly sales/Agent's monthly salary. Say for example, we have an agent who earns a salary of R1000 per month and the agent generates R1000 of sales for the month (of which application hosting = R500, web services = R300 and infrastructure hosting = R200). Salary cover = total monthly sales/agent's salary = R1000/R1000 = 1. This means that according to the commission table provided, the agent falls into the first category because salary cover <= 1. Based on the commission percentages in the table for the first category, the agent's commission for the month would then be R500*10% + R300*15% + R200*7% = R109 Let's say instead of R1000, this agent made R2000 worth of sales for the month (application hosting = R1000, web services = R600 and infrastructure hosting = R400). Then salary cover = total sales/agent's salary = R2000/R1000 = 2. This means that the agent would fall into the fourth category since salary cover >= 2. The agents commission would then be R1000*100% + R600*100% + R400*50% = R1800 Commission is calculated on 1 month's service revenue. So if an agent sells an annual product of 500, commission is not calculated on 500, but on 500/12. SSL commission is calculated on 2 months revenue and domains are not included. VAT needs to be subtracted before calculating the monthly service revenue as the data is VAT inclusive. Please find data set here: https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true And pbix: https://drive.google.com/file/d/1dGgyQ185t5uJjGZgNPbNyw71jVWvkHLu/view?usp=sharing
Cumulative Total data points with no data (0s) display as the maximum issue
I am trying to develop a Ticket Sales cumulative line graph sectioned out by how many weeks before the event the tickets were bought. I used the following DAX command to get the cumulative total. And here is the graph that results from this measure. The issue that appears is the data points of 0 display as the maximum, or what is known as the final total at 0 Weeks Out from the event. What is even more confusing is that the cumulative total measure recognizes those data points as 0s because let's say the final total is 100; those 0s show up as 100 but the measure does not count them because it knows they are 0s. This problem is even more apparent when I insert a legend. All of these spikes are weeks where no tickets were sold, and instead of maintaining the past week's total, they spike to the maximum (final total) and are not included in the measure's calculation. I have also considered excluding these data points that spike, since they would be 0 anyway, but there are over 1500 that would need to be excluded and after 50, PowerBI gives me an error. Is there anything I can include in my DAX command to make these "spikes" smooth out and carry over the past week's total?
Sales Amount at transaction level (aggregate at higher level) measure
Hello, I am trying to create a measure that displays the Sales Amount at transacttion (opportunity) level, regardless of the other dimensions I pull in my report. This can be a calculated column and it will work just fine, but I need to use a parameter with this calculation in the future so it HAS TO BE A MEASURE. When I do this calculation: Sales Amount Oppty Level = CALCULATE([Sales Amount],REMOVEFILTERS(Prod[ProdName]),VALUES(Oppty[OpptyName])) i get the accurate amount at opportunity level, but, if i add dimensions to the report, I get a cartesian product (all dimension values x all dimension values). ALLEXCEPT works only when I use the Product Table Oppty Name dimension, but I need to use Oppty Name from Product Table and the Partner dimension will make cartesian product with Opportunity for some reason. Cannot Use Oppty Name from Oppty table since then the measure will show the total sales amount for all Oppty for every row. Sales Amount Oppty Level 2 = CALCULATE([Sales Amount],ALLEXCEPT(Prod,Prod[OpptyName])) If i switch ALLEXCEPT to the below: Sales Amount Oppty Level 3 = CALCULATE([Sales Amount],ALLEXCEPT(Oppty,Oppty[OpptyName])) I will get the Sales Amount, not the Oppty total for all rows. Please help me to see the total Oppty Amount in a visual where I can have Oppty Name, Prod Name, Partner without cartesian product, like (red is bad, since partners 1 and 2 are not connected to oppty 1): Please help! Here is the modelCalculating a dynamic benchmark with multiple conditions
Hi all, I am struggling with a problem and I hope you can help. My star schema looks like this: fact sales, dim product, dim country, dim store I am trying to achieve the following (mind you I have a composite model in my report with 2 DQ connections and I cannot use PowerQuery) I have a measure 'revenue'. Based on this measure I want to calculate a revenue share % for my products against the total revenue. Like: product A 20% product B 14% product C 0.4% This is achieved easily by a metric like: product revenue share % VAR total_revenue_fixed = calculate([revenue], REMOVEFILTERS(dim_product[product_name]) VAR revenue_product = [revenue] RETURN DIVIDE(revenue_product, total_revenue_fixedl) Now I want to add more complexity. When selecting a store from a report slicer, I want to see the product revenue share % for similar stores (say store group). The problem is that I need an average of my original product revenue share % per store group to show up per store id (mapped to a certain store group). So I need an average measure of an existing measure in a new context, which is of course not possible in PBI. I need to make sure that my store slicer(s) do not affect my original 'product revenue share %' and its components (like total revenue). How can I achieve this? I hope it's clear what I am asking.
Average over dates for benchmark
Hi there I am trying to understand the difference in sales pre and post event to see if there is any change. I have payment files for 6 dates, in a single table 'SaleFiles' With columns: SalesValue,SaleName,MembershipDate,DateKey,Customer ID Each customer ID may have multiple sales/refunds on the same day under different types of SaleName. i.e: £10,Membership,02/07/2022,44744,12345 £100,Ticket,02/07/2022,44744,12345 -£20,Refund,02/07/2022,44744,12345 I then have a date table: The dates I have are: 7 May 2022, 4 June 2022, 02 July 2022, 30 July 2022, 27 Aug 2022, 24 Sep 2022 With Columns: Date,DateKey These are joined on the date key. One (DateTable) to Many (SaleFiles) What I want to be able to achieve is, what was the average value of Ticket sales over the 7th May & 4th June combined. Then be able to put this into a table to show the difference between this average and each of the following dates. I would hope to use, DateTable as my date in table or x-axis as this will filter other information elsewhere. I would then also want to be able to drill through by customer to see if the value per customer has increased or decreased on each on after the 4th June. I've tried the following but I can't seem to get it all to stick so I can see the difference. It feels like this should be really simple but I've tweaked all the variations of FILTER | AND | ALL within the measure and for some reason it always ends up with a problem somewhere, either averaging every date seperately (not combining the first two) or returning blanks for any dates outside of the filter date. **UPDATE** I've got this far and the calculation works per customer until I try to display this over a date table. Any help greatly appreciated.Combining sales + forecast in one column; problems with getting the last sales date
I am trying to show a trendline in which the sales actuals line flows over in a forecast. I found a way to do this online, but get errors in the first part already when I try to obtain the last sales date. The instruction video I am using is: https://www.youtube.com/watch?v=DKgF-5QHY68. In the first part of the DAX, I need to obtain the last sales date. This formula is not working (I believe) because I have the Sales Amount and the Forecast in the same table, and thereby the last sales date is in fact, the last forecast date (see table below for example): Finance Date Sales Amount Forecast Budget 10-2021 100 150 120 11-2021 150 200 140 12-2021 0 180 150 12-2022 0 200 120 I also tried to calculate this with a simple IF statement, but although it seems in a table it is working, when I plot it in a line chart it messes up all the data. Example of that IF statement: Sales + Forecast = IF[Sales Amount] = 0, result if true [Forecast], result if false [Sales Amount]. What should I do? Should I change the DAX formula or should I change the data model and split the main table into three different tables (sales table, forecast table, budget table). Pls help, I am very lost...Target Sales Percentage based on Store ID in DAX
Hello All, I have the below data set for sales for which I am trying to get a percentage of sales in measure. Store ID Sales Date Daily sales Total Employees 101 1/1/2021 5 9 101 1/1/2021 8 9 101 1/1/2021 10 9 102 1/1/2021 7 11 102 1/1/2021 5 11 102 1/1/2021 11 11 102 1/1/2021 8 11 102 1/1/2021 12 11 I want to have Perctenage KPI which calculates Store Total Sales / Total Employees. For example, for Store 101 my measure will return (5+8+10)/9 = 2.55 so on and so forth... By default, I would like to have a value that returns data for all stores. In above example. All the sales will be summed (66) and then divided by (9+11) = 20 and the result should show as 3.3 Any guidance will be helpful. Thanks,Average of a Measure for a period of time
I have a measure called "First30Days" that goes back for 30 days of the Daily Sales and summarizes 30 days of sales for each calendar day. (I know the dataset is not every calendar day, that is ok.) First30Days = CALCULATE(('SalesTable'[DailySales]),DATESINPERIOD('Date Dim'[Date],NEXTDAY('Date Dim'[Date])-30,30,DAY)) As the screenshot indicates, this works fine and as expected. What I can't seem to figure out is how to average the First30Days column over 30 calendar days. I should end up having a new measure that shows me the 30 day average of the First30Days. Based on the screenshot, i should get 647,547 in my new column on the last row. (And, I will get a result in each row, but I am only showing for that one day in this example.) Year Quarter Month First 30 Days AVERAGE OF First 30 Days 2020 Qtr 2 June 22 $492,785 2020 Qtr 2 June 23 $515,496 2020 Qtr 2 June 24 $563,194 2020 Qtr 2 June 25 $589,350 2020 Qtr 2 June 26 $581,721 2020 Qtr 2 June 29 $612,746 2020 Qtr 2 June 30 $647,252 2020 Qtr 3 July 1 $660,788 2020 Qtr 3 July 2 $669,226 2020 Qtr 3 July 3 $629,239 2020 Qtr 3 July 6 $649,151 2020 Qtr 3 July 7 $680,475 2020 Qtr 3 July 8 $678,310 2020 Qtr 3 July 9 $679,352 2020 Qtr 3 July 10 $671,994 2020 Qtr 3 July 13 $678,355 2020 Qtr 3 July 14 $718,676 2020 Qtr 3 July 15 $732,290 2020 Qtr 3 July 16 $733,909 2020 Qtr 3 July 17 $704,970 2020 Qtr 3 July 20 $709,198 647,547
