Forum Discussion

norbi's avatar
norbi
Helper 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
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI norbi

 

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

 

Currently we get

 

 

 

 

 

norbi's avatar
norbi
Helper 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
    Community 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] )
        )
    )