Create a calculated table with an input column and calculated column

[AmiraBedh]

I want to create a table in Power BI like below :

Product Year  Count

A           2020 100

B           2021  569

C           2021    99

The product is an input column which means I provide the name in the creation of the calculated table.

The Year is the group by column and the count is the a measure : counting the rows of the product A by Year

[Anonymous · Accepted solution]

Hi @AmiraBedh ,

You could use SUMMARIZE() function.

https://docs.microsoft.com/en-us/dax/summarize-function-dax 

Since the product is an input column, you will need to filter the table for each product before summarize it. Ie create multiple summarized table based on products then union the summarized tables.

For example:

new_table = 
var product_a = SUMMARIZE(FILTER(Table,Conditon1),[Year]"product","A",COUNT([column]))
var product_b = SUMMARIZE(FILTER(Table,Conditon2),[Year]"product","B",COUNT([column]))
return
union(product_a,product_b)

If I misunderstood your meaning, please share some sample data and expected result.

Best Regards,

Jay

[amitchandak]

@AmiraBedh , Last two are easy, they can be driven using group by power query or summarize of DAX.

How do you want calculate the product, that you need to tell to suggest solutions