Forum Discussion

RobbeVL's avatar
RobbeVL
Impactful Individual
6 years ago

Creating a Calculated table with multiple levels

Hi all,

 

A little help with creating this table would be nice.
I have quite a complex dataset, but I would like to complete this "simple" DAX measure. 

I want a Calculated Table with the following columns:

Unit ('BU'[BusinessUnit])
Year ('Date'[Year])
SalesRate (Measure)
SalesRateLY (Same measure, but showing the previous year)

Please take into account that the Last 2 columns would be based on measures refering to multiple columns.
How do I build up this measure ? 

Robbe


Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Can you share sample data and sample output.

    Appreciate your Kudos.

    • RobbeVL's avatar
      RobbeVL
      Impactful Individual
      6 years ago

      Hi amitchandak ,

      I dont really know how to explain anymore. This is the way someone forwarded it to me. Seemed clear to me.
      The result should be something like this:

      BU is a Dimension, Year is from a dimension, but Sales is a measure based on 5+ tables with references to multiple other measures)
      If I would drag and drop this in a matrix table, i'd get the result quite easy.

      But how to "hardcode" this in a Calculated table? 


       

      BU Year Sales SalesLY
      Unit 1 2019 1000  
      Unit 1 2020 2000 1000
      Unit 2 2019 1500  
      Unit 2 2020 2000 1500

       

      Regards,

       

      Robbe

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    The information you have provided is not clarifying the problem for me. Please explain with an example.

    You can share sample and sample output data.

    Appreciate your congratulations.

    • RobbeVL's avatar
      RobbeVL
      Impactful Individual
      6 years ago

      I seem to get the correct result using the following:

      SUMMARIZE(
              GENERATE ( 'Date'; 'Business Unit' );
               'Date'[Year];
               'Business Unit'[DIM_BUSINESSUNIT_ID];
                "Sales"; [SalesRate];
                "SalesLY; ???)

       

      But how do I show the Sales of last year in this summarize?

       

      Robbe

      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi RobbeVL ,

         

        As we know we cannot create dynamic calculated column like that. Could you please tell me what is your formula of your measure [SalesRate]? Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.