Forum Discussion

norbi's avatar
norbi
Icon for Helper I rankHelper I
8 years ago
Solved

Using Ratios between two tables for output depending on Filter

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 Ye...
Need Help
norbi's avatar
norbi
Icon for Helper I rankHelper I
8 years ago

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    
ShopCustomer_TypeTarget_1Target_2Target_3
shop_1Customer_Type_1                               55.56                         111.11                         222.22
shop_2Customer_Type_1                               44.44                            88.89                         177.78

 

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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] )
        )
    )