Percent of Subtotal is Incorrect When Headcount Changed Dynamically using Parameters

[ShejalTiwari]

Datasource

Task.xlsx file is the data Source which contains data including Group, Function, Country, and Headcount.

.pbix file  - https://drive.google.com/file/d/1mhLFS0-BvzScHx-O5FUIqMRZG_ehCqgI/view?usp=sharing

.xlsx file - https://docs.google.com/spreadsheets/d/1A4SPTjLhfiL691StfF0nuJA1K2rIeoE9/edit?usp=sharing&ouid=10869...

Problem

Referring to .pbix file, Taking function1 as the example, function1 have the presence in three countries and their Headcounts are given having a total of 69(41+17+11). Concentration% Country is calculated as (41/69)*100=59.42% for India. Similarly for USA and Philippines.

I have 4 parameters in place, If I select "India" in Parameter "Country 1" and 10 in "Headcount % Change1", Its means I am increasing the Headcount of India by 10% which gives me 41*(1+10/100) = 45.1. Similarly If I select "USA" in Parameter "Country 2" and 5 in " Headcount % Change 2", Its means I am increasing the Headcount of USA by 5%. HC for Philippines will remain the same.

Find out the Concentration Percent for the new dynamic headcount which is being changed by parameters.

Expected result

Concentration % -

India =45.1/(45.1+17+11.55) = 61.23

USA 11.55/(45.1+17+11.55) = 15.68

Philippines = 17/(45.1+17+11.55) = 23.08

Attached Excel and .pbix file link for your reference

[ShejalTiwari]

Thankyou so much Zhu for helping in solving the issue. I really appreciate your time and effort 🙂

[v-linhuizh-msft · Accepted solution]

Hi @ShejalTiwari ,

Please create a measure as follows:

Measure 2 = 
VAR _a = SELECTEDVALUE(Sheet1[Group])
VAR _table = SUMMARIZE(ALL(Sheet1),'Sheet1'[Group],'Sheet1'[Function],'Sheet1'[Country],"Test",[Headcount New])
RETURN
IF(
    HASONEVALUE(Sheet1[Group]),
    DIVIDE([Headcount New], SUMX(FILTER(_table, 'Sheet1'[Group] = _a), [Test])),
    SUMX(SUMMARIZE(Sheet1,Sheet1[Group],"Measure", DIVIDE([Headcount New], SUMX(FILTER(_table, 'Sheet1'[Group] = MAX(Sheet1[Group])), [Test]))),[Measure])
)

The result is as follows:

Best Regards,
Zhu
Community Support Team

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