Using Ratios between two tables for output depending on Filter

[norbi]

Hi,

I would truly appreciate some assistance with a challenge I have been struggling with in Power BI. I would like to get the Customer_Number ratios in Table_1 to split the Year_1, Year_2 and Year_3 numbers. Here is the example:

Table_1
Shop	Customer_Type	Customer_Numbers
shop_1	Customer_Type_1	10
shop_1	Customer_Type_2	20
shop_1	Customer_Type_3	50
shop_2	Customer_Type_1	8
shop_2	Customer_Type_2	6
shop_2	Customer_Type_3	50
shop_3	Customer_Type_1	30
shop_3	Customer_Type_2	150
shop_3	Customer_Type_3	200

In Table_1 I have three different Shop's with three different Customer_Types split in numbers.

Table_2
Customer_Type	Year_1	Year_2	Year_3
Customer_Type_1	100	200	400
Customer_Type_2	150	250	500
Customer_Type_3	170	300	370

In Table_2 are the Year_1, Year_2 and Year_3 desired numbers to achieve.

Power BI Output
Shop	Customer_Type	Target_1	Target_2	Target_3
shop_1	Customer_Type_1	20.83	41.67	83.33
shop_1	Customer_Type_2	17.05	28.41	56.82
shop_1	Customer_Type_3	28.33	50.00	61.67
shop_2	Customer_Type_1	16.67	33.33	66.67
shop_2	Customer_Type_2	5.11	8.52	17.05
shop_2	Customer_Type_3	28.33	50.00	61.67
shop_3	Customer_Type_1	62.50	125.00	250.00
shop_3	Customer_Type_2	127.84	213.07	426.14
shop_3	Customer_Type_3	113.33	200.00	246.67

The Targets in the Power BI Output table should be derived from Table_1 and Table_2 in Power BI. However it should also automatically change this as the filters change e.g. if I only filter on Customer_Type_1 & 2 or Shop_1 & 3 the split should be calculated to achieve the Target_1, Target_2 and Target_3.

Any assistance will be truly appreciated.

[Zubair_Muhammad]

HI @norbi

See the attached file. Hope it helps

[Zubair_Muhammad]

@norbi

Here are the Steps

1) Create the relationship between two table using Customer_Type

2) Add the Following Calculated Column in Table 1 to get the %ages on which Targets will be determined

%_age =
DIVIDE (
    Table1[Customer_Numbers],
    CALCULATE (
        SUM ( Table1[Customer_Numbers] ),
        ALLEXCEPT ( Table1, Table1[Customer_Type] )
    )
)

3) Add the Calculated Columns for Targets

Target 1 =
RELATED ( Table2[Year_1] ) * Table1[%_age]

 and so on for Targets 2 and 3

[norbi]

@Zubair_Muhammad

Thank you very much for the feedback and information. The calculation works if you use all e.g. Segments and Shops, however if you would like to see the impact if you take away one Shop or Segment the number remain and do not update for the once selected only. SO what I would like to achieve is to take the Year_1 Numbers and they need to total to the selection made, also if only one or two Shops or segments are selected.

[Zubair_Muhammad]

HI @norbi

What result do you expect when for example user selectd Customer Type 1?

Currently we get

[norbi]

@Zubair_Muhammad

Yes that works 100% agree. So as an example if you select Customer_Type_1 and Shop_1&2 I would like to get the 100|200|400 result however now only split between Shop_1 and Shop_1:

Current Results:

Desired Output:

Power BI Output
Shop	Customer_Type	Target_1	Target_2	Target_3
shop_1	Customer_Type_1	55.56	111.11	222.22
shop_2	Customer_Type_1	44.44	88.89	177.78

[Zubair_Muhammad]

Hi @norbi

Please see the revised file here

Lets use these MEASURES

%age =
DIVIDE (
    CALCULATE ( SUM ( Table1[Customer_Numbers] ) ),
    CALCULATE ( SUM ( Table1[Customer_Numbers] ), ALLSELECTED ( Table1[Shop] ) )
)

Tar1 =
[%age]
    * CALCULATE (
        VALUES ( Table2[Year_1] ),
        FILTER (
            ALL ( Table2 ),
            Table2[Customer_Type] = SELECTEDVALUE ( Table1[Customer_Type] )
        )
    )

[Zubair_Muhammad · Accepted solution]

@norbi

File is attached here

[norbi]

@Zubair_Muhammad Thank you very much it works, it is truly appreciated.