database
2 Topics
Splitting data out over a predefined percentage and using that data for new calculations
Hi BI Community I'm having difficulties to visualize an important feature in our reports. I used to have the data in a fully different format, in the new environment I'm unable to do SQL queries on my data so I'm bound to Power BI solutions. Part of my issue already begins at my splitted orders table. Splitted orders 1 Order No Owner 1 Owner 2 Percentage ORDER1 Person1 Person2 50 ORDER3 Person3 Person1 75 I manually made Splitted orders 2 out of the data from splitted orders 1 as I wasn't able to have any code do the trick in Power BI itself. Splitted orders 1 is actually a dynamic table that's getting data from our employees, they can add but also modify/delete. Is there a way I can get DAX or Power Query to give me the values as in splitted orders 2 or is there no other option than to rebuild the system to which they enter their data? Or perhaps my issue is't beginning here like I thought it was and I can keep splitted orders 1. Splitted orders 2 Order No Owner Percentage ORDER1 Person1 50 ORDER1 Person2 50 ORDER3 Person3 75 ORDER3 Person1 25 Situation: We have sales orders, they are being split out in a header table and a lines table. Every sales order is property of one owner. Order Header Order No Owner ORDER1 Person1 ORDER2 Person2 ORDER3 Person3 In special cases an order can get 'splitted', meaning that the total amount of the order gets devided over a specific percentage and two owners. (see the issue on the top about splitted orders, here I used the splitted order 2) Splitted orders Order No Owner Percentage ORDER1 Person1 50% ORDER1 Person2 50% ORDER3 Person3 75% ORDER3 Person1 25% I thought that making a full join on the splitted orders and the headers, along with the new headers and the lines would help me fix the issue. Yet this doesn't seem like a best practice to me as this is making the entire report extremely slow. So I continued to look for a different solution. I kept the full joined headers and calculated the lines based on the percentage of the splitted order. But I'm unable to get my sums to work that way. As I cannot calculate the field without making a sum() of all values. Order No Owner Percentage ORDER1 Person1 50% ORDER1 Person2 50% ORDER2 Person2 100% ORDER3 Person3 75% ORDER3 Person1 25% Meaning I made a calculated field AmountSplit = sum([Amount]) * sum('SplitHeader'[Percentage]) This works for me on the order level but obviously I need to keep the correct sums if I start combining my orders. Order No Product Amount ORDER1 Product1 10 ORDER1 Product2 20 ORDER2 Product1 10 ORDER2 Product2 10 ORDER2 Product3 10 ORDER2 Product4 10 ORDER3 Product1 20 ORDER3 Product2 10 ORDER3 Product3 20 If I would want to show the total amounts per person I'd need to get the AmountSplit1 result un-summarized. The DAX code makes sure I'm having AmountSplit2. I'm unable to remove the SUM part as it's no longer calculating after that. Owner Amount AmountSplit1 AmountSplit2 Person 1 30 27,5 60 Person 2 40 55 105 Person 3 50 37,5 37,5 Also tried making a calculated column in my lines both using SUM and no SUM. In the example I summarised the tables with only the basic data that has to be recalculated but there are a bunch of other fields in both header and lines that I need for calculations. Anyone who might have the magical solution? Thanks!Solved6.1KViews0likes14CommentsIs there an easy way to split out targets over months based on fixed percentage?
Hi power bi community, I'm searching for an efficiënt way to create my monthly targets in power bi. It seems like such an 'easy' question but I cant wrap my head around it. Probably making it a lot more difficult than it should be. Currently I have everything stored in yearly targets (& types) per salesperson. For the example I narrowed it down to users only. Salesperson target 2019 target 2020 target 2021 Person 1 100.000 200.000 100.000 Person 2 200.000 150.000 100.000 Person 3 100.000 100.000 200.000 Person 4 250.000 200.000 250.000 Now I want to spread it out over 'monthly' targets. There is a 'fixed' percentage of those yearly targets that is needed per user per month. Which I also stored in a table so I can change those easily if the estimated percentages would change over time. Month percent 1 5% 2 10% 3 10% 4 11% Which leads me to having to create somthing like this, I think thats the easiest way to use the data in different graphs to split out my monthly target over monthly sales. YYYY MM Salesperson month target 2019 1 Person 1 5.000 2019 1 Person 2 10.000 2019 2 Person 1 10.000 2019 2 Person 2 20.000 2020 1 Person 1 10.000 2020 1 Person 2 7.500 2021 1 Person 1 5.000 2021 1 Person 2 5.000 Is there a formula or easy way to achieve this? I feel like it shouldn't be this hard to build / calculate this table. The 'target table' and the 'percentage' table are manually entered tables and the 'Date' table is a calculated one in power bi. Which means I can't run an SQL query with cases as an import statement. I don't want to have to manually start working out all the calculations in excel so I can copy paste that into a new table. It would also limit my abilities to change the monthly percentages in the future, making me have to recalculate everything. I've seen something remotely similar but that goes from months to days with a crossjoin but in this case the percentage I need per month is already pre-defined and it shouldn't generally split out over all months. Already tried making the sum per year and show those in a table next to the dates so I could split those out over the percentages per month but then I miss my sales people. Which is also required to watch it on person level not only month level.Solved2.5KViews0likes2Comments