Deduping data at higher levels

Question

Hi&nbsp; Everyone&nbsp;I'm new to Power BI and DAX, hence finding one issue as a bottle neck for my analysis. I have the data like in the below table&nbsp;Person&nbsp; &nbsp; &nbsp;Region&nbsp; &nbsp; &nbsp; District&nbsp; &nbsp; &nbsp;Territory&nbsp; &nbsp; &nbsp; SalesP1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100P1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100P1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100P1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100P1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; R2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100------------------------------------------------------Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $500&nbsp;If I remove, Territory from the above table then Total should be $300 and If I remove District, then Total should be $200 and If I remove Region as well, then for Person P1, Total should be $100.&nbsp;How we can achieve this&nbsp;Thanks,Sree

stachu · Answer

I assume that the example you posted is describing the desired output, correct?How does the the&nbsp;raw data input look like? could you share the examples of the tables?

jaladurgam · Answer

Hi Stachu,Thanks for your reply&nbsp;I have a dimension table (Geography) and fact table Sales as below..&nbsp;Dim_Geo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SalesGeo_KeyRegionDistrictTerritory&nbsp;

jaladurgam · Answer

Hi Stachu,Thanks for your reply&nbsp;I have my dimension tables (Contact and Geo) and fact table Sales as below..&nbsp;Dim_Geo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Fact_Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dim_ContactGeo_Key&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Geo_Key&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Contact_KeyRegion&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ContactNameDistrictTerritory&nbsp;Output should be like this..&nbsp;Query1:ContactName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Region&nbsp; &nbsp; &nbsp; &nbsp; District&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Territory&nbsp; &nbsp; &nbsp; &nbsp; SalesContact1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100Contact1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $200&nbsp;Query2:ContactName&nbsp;&nbsp;&nbsp;

jaladurgam · Answer

Hi Stachu,Thanks for your reply&nbsp;I have my dimension tables (Contact and Geo) and fact table Sales as below..&nbsp;Dim_Geo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Fact_Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Dim_ContactGeo_Key&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Geo_Key&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Contact_KeyRegion&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ContactNameDistrictTerritory&nbsp;Output should be like this..&nbsp;Query1:ContactName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Region&nbsp; &nbsp; &nbsp; &nbsp; District&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Territory&nbsp; &nbsp; &nbsp; &nbsp; SalesContact1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100Contact1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;T2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $200&nbsp;Query2:ContactName&nbsp; &nbsp; &nbsp; Region&nbsp; &nbsp; &nbsp;District&nbsp; &nbsp; &nbsp; &nbsp;SalesContact1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;R1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; D1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$100&nbsp;Having said that, Total should be automatically deduped at the higher levels... Sometimes we have duplicate data at District level as well, in that case data should be deduped at Next higher level at Region Level...&nbsp;How this can be solved with single fact attribute (Sales). By the way, I'm providing Self Service BI, so there is no cushion to play around with the canned report.&nbsp;Sreeni

v-chuncz-msft · Answer

jaladurgam,
&nbsp;
You may try adding multiple measures instead.

Forum Discussion

Deduping data at higher levels

5 Replies

Related content

Help with deduping code

Fabric-Price higher then assumed

Display values from higher hierarchy levels

Exclude items from distinct count on higher level of aggregation

Subtotaling at higher level