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

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