Forum Discussion

AgencyPowerBi's avatar
AgencyPowerBi
Helper III
5 years ago

How to calculate different columns based on multiple rules

Hi, I have a problem which is two fold:

 

1. I have huge table with different target columns, that I would unpivot and use one column.

           Problem is that I have too many rows, which makes upivoting impossible.

2. I need to calculate values from these tagret colums in one calculated column based on multiple conditions.

           Different tagets are used for the same client based on time.. so in 2018. I need to take value from one column, in 2019. for the same client active is another column.

 

So I have defined client table, with start and end dates and name of the column used in that period, but I cannot substitute text for the name of the column. Or can I?! 

 

Is there a way to do this?

 

Thank you 🙂

 

Need Help

4 Replies

    • AgencyPowerBi's avatar
      AgencyPowerBi
      Helper III
      5 years ago

      Hi,

      I have attached excel file with three tables.

       

      Table sheet is main table with all data.

      Buying_Channel_def is table that determines name of the Channel that goes into Buying_Target_def table.

       

      Buying channel and Buying target are connected with a relationship, as well Table (connects to Media column).

       

      As you can see there are few conditions that have to be met. Start time determines Buying channel name, and then Date determines the name of the column that would serve as a source to calculate sum.

       

      Table sheet cannot be unpivoted beause its number of rows 😞

       

      Calculation groups, I just started using, so I see no way to make this calculation.. If you know a way.. 

      Many thanks!

       

      Table 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi AgencyPowerBi 

        I see the three tables you are dealing with, and I am confused about the data model and your calculate logic.

        Please show me the relationships between three tables. And your Table is blank, I couldn't see the value in Media column.

        Can you explain your calculation logic to me in more details? Just like firstly we will get result or filter by the value from which table, and then and finally got the result. It is better for you to show me the logic by specific data. You can show me a screenshot of the result you want.

         

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.