Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Multiple conditional calculations with shared Excel file

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

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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".

relation.JPG

 

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.

mea.JPG

 

 

Best regards

Icey

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Icey
Community Support
Community Support

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".

relation.JPG

 

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.

mea.JPG

 

 

Best regards

Icey

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Anonymous
Not applicable

Thank you @Icey !

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please check if the attached .pbix file could meet your requirement.

store.JPG

 

 

Best regards

Icey

 

If this post helps,then consider Accepting it as the solution to help other members find it faster.

Icey
Community Support
Community Support

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

 

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.