Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a sales report (from a live connection) that I need to split between 2 distribution channels. I know what’s the split for all of them (used conditional column for that) except 2 customers.
For those 2 customers I get a % with distribution channel split that changes every month.
This is the sample of the % split:
|
| eComm | |
Year | Month | store 1 | store 2 |
2019 | 1 | 9.6% | 32.6% |
2019 | 2 | 11.9% | 42.7% |
2019 | 3 | 12.5% | 33.6% |
What I want to do is calculate for those 2 clients the % split for each year & month, and then add each portion to the right group (eComm/Stores). For example, store 1 on 2019-01 has 9.6% eComm so the rest (90.4%) needs to be added to the stores group. The end user will have only 2 options to choose from (online/stores).
What will be the best way of doing something like this? I connected a shared excel so users can update the % once a month so the split calculation needs to be derived from that file (maybe with a If statment and a lookup?)
Many thanks
Solved! Go to Solution.
Hi @Anonymous ,
According to my experience, when we turn off the error reported by Power BI Desktop, the file will be opened normally. What's yours version?
In addition, let me explain what I did in the .pbix file.
1. Orginal tables.
Table:
Date | Customer | Items | Sales amount | units amount | Channel |
1/1/2020 | 123 | A | 100 | 10 | Store |
1/2/2020 | 456 | B | 100 | 10 | Both |
1/4/2020 | 789 | C | 100 | 10 | Both |
1/9/2020 | 1011 | D | 100 | 10 | Online |
eComm:
Year | Month | store 1 | store 2 |
2020 | 1 | 9.60% | 32.60% |
2020 | 2 | 11.90% | 42.70% |
2020 | 3 | 12.50% | 33.60% |
2. Create anotehr "Channel" table by "Enter data".
Channel_ |
Store |
Online |
3. Create a "YearMonth" column in "eComm" table.
YearMonth = [Year] & FORMAT ( [Month], "00" )
4. Create relationship between "eComm" table and "Table".
5. Create a measure.
Measure =
VAR t =
ADDCOLUMNS (
FILTER (
CROSSJOIN ( 'Table', Channel ),
[Channel] = [Channel_]
|| [Channel] = "Both"
),
"Sales_",
IF (
[Channel] = "Both"
&& [Channel_] = "Store",
[Sales amount] * LOOKUPVALUE ( eComm[store 1], eComm[YearMonth], [YearMonth] ),
IF (
[Channel] = "Both"
&& [Channel_] = "Online",
[Sales amount]
* ( 1 - LOOKUPVALUE ( eComm[store 1], eComm[YearMonth], [YearMonth] ) ),
[Sales amount]
)
)
)
RETURN
SUMX ( t, [Sales_] )
Then, you'll get what you want.
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi @Icey ,
Thank you but I can't open the file because it says I need a newer version of PBI (can't upgrade because my work restrictions).
Can you save it as the previous version please?
Many thanks
Hi @Anonymous ,
According to my experience, when we turn off the error reported by Power BI Desktop, the file will be opened normally. What's yours version?
In addition, let me explain what I did in the .pbix file.
1. Orginal tables.
Table:
Date | Customer | Items | Sales amount | units amount | Channel |
1/1/2020 | 123 | A | 100 | 10 | Store |
1/2/2020 | 456 | B | 100 | 10 | Both |
1/4/2020 | 789 | C | 100 | 10 | Both |
1/9/2020 | 1011 | D | 100 | 10 | Online |
eComm:
Year | Month | store 1 | store 2 |
2020 | 1 | 9.60% | 32.60% |
2020 | 2 | 11.90% | 42.70% |
2020 | 3 | 12.50% | 33.60% |
2. Create anotehr "Channel" table by "Enter data".
Channel_ |
Store |
Online |
3. Create a "YearMonth" column in "eComm" table.
YearMonth = [Year] & FORMAT ( [Month], "00" )
4. Create relationship between "eComm" table and "Table".
5. Create a measure.
Measure =
VAR t =
ADDCOLUMNS (
FILTER (
CROSSJOIN ( 'Table', Channel ),
[Channel] = [Channel_]
|| [Channel] = "Both"
),
"Sales_",
IF (
[Channel] = "Both"
&& [Channel_] = "Store",
[Sales amount] * LOOKUPVALUE ( eComm[store 1], eComm[YearMonth], [YearMonth] ),
IF (
[Channel] = "Both"
&& [Channel_] = "Online",
[Sales amount]
* ( 1 - LOOKUPVALUE ( eComm[store 1], eComm[YearMonth], [YearMonth] ) ),
[Sales amount]
)
)
)
RETURN
SUMX ( t, [Sales_] )
Then, you'll get what you want.
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hi @Anonymous ,
Are those two options (online/stores) included in your data?
What is the structure of your data?
Can you share some sample data with the same structure as the real data?
Best Regards,
Icey
Hi @Icey ,
The channel isn’t include in my raw data, I know the split for each client except 2 which are both channels. I created a column with conditional rule in order to add the channel split. The data looks like this:
Date | Customer | Items | Sales amount | units amount | Channel |
01/01/2020 | 123 | A | 100 | 10 | Store |
01/02/2020 | 456 | B | 100 | 10 | Both |
01/04/2020 | 789 | C | 100 | 10 | Both |
01/09/2020 | 1011 | D | 100 | 10 | Online |
I want to create a rule that if the customer is 456/789, then sales and units amount multiply by a % which will give me the online portion for that customer. Then I want to sum that portion into the online group and the same goes for the store portion. I have 2 customers that are built like that.
The challenge is that I have a different % split for those 2 customers which changes every month. I created another table connected to a shared Excel so each month I just need to plug in the % and PBI will refresh that portion for those 2 customers. Each month on that table has a unique identifier (month & year – i.e. 01-2020) which can be used with a LOOKUP (if that’s the way to go).
What I'm after is the best solution to create the split rule for both of those customers, based on a monthly changing %.
Should this be done in PQ editor (and if yes - how?) or maybe as a calculated column in the PBI Desktop itself?
What will be the best way to solve this one?
Many thanks!
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |