Forum Discussion
Add datarows using parameters in Power BI
Can you please help me with below problem?
I have group, function, level , LOB, country ,headcount and few more columns
in my data putup in a matrix as shown. Currently making use of only group, function, level ,
LOB, country, headcount. Every function have headcount present in different countries. I created two
parameters for country and headcount. I want a functionality by which if I select a country using parameter 1 and headcount
using parameter 2. One extra row of country should be added to each function if already that country is not present and
headcount should be allocated from the parameter 2. For example if I have function as "function1" and headcount is available in
country India, Philippines, USA with headcount as 65,21,20 respectively. If I select China in parameter 1 and select headcount
as 10 in parameter 2 then that function should have 4 countries as India, Philippines, USA,China with headcount as 65,21,20,10 respectively in matrix and China should be added in the columns. So for the given Example with Group CBB, Level A, LOB X, function 1 should add one more country China with headcount 10 using the parameters.
I have attached workbook and data for Your reference.
.pbix file attached - https://github.com/Shejalt/Power-BI-files/tree/main
Anonymous
Hi ShejalTiwari ,
According to your description, adding a column to an already fixed matrix is not supported in power bi. If you want to add a new column it is equivalent to adding several rows to the data, which is not possible with data that is already fixed. But there is a way you can try it. You can create a new matrix and display the measure separately.SelectedCountry = VAR _SelectedCountry = SELECTEDVALUE('Select Country 1'[Country]) VAR _CurrentCountryList = VALUES(Sheet1[Country]) VAR _isAdded = IF( _SelectedCountry IN _CurrentCountryList, 0, SELECTEDVALUE('HC % Change 1'[Headcount % Change 1]) ) RETURN _isAddedSum of head count = SUM(Sheet1[Headcount])Total = [SelectedCountry] + [Sum of head count]Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi ShejalTiwari ,
According to your description, adding a column to an already fixed matrix is not supported in power bi. If you want to add a new column it is equivalent to adding several rows to the data, which is not possible with data that is already fixed. But there is a way you can try it. You can create a new matrix and display the measure separately.SelectedCountry = VAR _SelectedCountry = SELECTEDVALUE('Select Country 1'[Country]) VAR _CurrentCountryList = VALUES(Sheet1[Country]) VAR _isAdded = IF( _SelectedCountry IN _CurrentCountryList, 0, SELECTEDVALUE('HC % Change 1'[Headcount % Change 1]) ) RETURN _isAddedSum of head count = SUM(Sheet1[Headcount])Total = [SelectedCountry] + [Sum of head count]Final output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the solution.
