Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic Table in DAX expression

Hello All,

I have the below dataset of products and store information. I would like to have a simple DAX Expression which returns Product and total sales.

Main Dataset:

CityProduct AProduct BProduct CProduct D
New York2356
Florida4545
Toronto5261
Boston7143

 


The expected result in DAX expression:

Product TypeTotal Sales
Product A18
Product B11
Product C19
Product D15



I don't mind writing hardcoded values for the Product. Basically, I would like to write some DAX expression like

Create new table

 > Add row ( "Product A", Sum(ProductA),

 > Add row ( "Product B", Sum(ProductB)

Return Table.

I don't want to create a separate calculated table as it doesn't sync with the main dataset on using a filter so looking to created Dax expression which will return the table and also remains in sync with the main dataset so whenever the filters are changed my values in the Dax table also changes.

Thanks in advance for the help.

 

DAX
Table

9 Replies

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    5 years ago

    Hi Anonymous 

     

    This seems quite straight forward, please go into query editor and select your product columns and unpivot them,
    you'll get a desired result and for this you might not require dax as well, just drag the columns and it would sum up your data or you can use measure as well.

     

    Thanks, 
    Ankit Kukreja
    www.linkedin.com/in/ankit-kukreja1904

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Ankit,

       

      Thanks for replying. In my case the unpivot option won't work so looking for a measure option here which returns the product and it's total. Will be great if you can tell me the DAX expression for the same.

       

      Thanks,

      Jay

      • AnkitKukreja's avatar
        AnkitKukreja
        Super User
        5 years ago

        Hi

        Not sure what you were looking for exactly, but unpivot would be the best approach considering the scenario you have shared here. But, if you've some other requirements, then please use below dax for getting the same results.

        Sales by Product =

        UNION(

        SELECTCOLUMNS( 'Table (2)' , "Product A" , "Product A" , "Sales" , 'Table (2)'[Product A] ),

        SELECTCOLUMNS( 'Table (2)' , "Product B" , "Product B" , "Sales" , 'Table (2)'[Product B] ),

        SELECTCOLUMNS( 'Table (2)' , "Product C" , "Product C" , "Sales" , 'Table (2)'[Product C] ),

        SELECTCOLUMNS( 'Table (2)' , "Product D" , "Product D" , "Sales" , 'Table (2)'[Product D] )

        )


        Thanks,
        Ankit Kukreja
        www.linkedin.com/in/ankit-kukreja1904