cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Deduping data at higher levels

Hi  Everyone

 

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

 

Person     Region      District     Territory      Sales

P1            R1               D1           T1              100

P1            R1               D1           T2              100

P1            R1               D2           T3              100

P1            R1               D2           T4              100

P1            R2               D3           T5              100

------------------------------------------------------

Total                                                            $500

 

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.

 

How we can achieve this

 

Thanks,

Sree

5 REPLIES 5
Super User I
Super User I

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Hi Stachu,


Thanks for your reply

 

I have a dimension table (Geography) and fact table Sales as below..

 

Dim_Geo              Sales

Geo_Key

Region

District

Territory

 

Hi Stachu,


Thanks for your reply

 

I have my dimension tables (Contact and Geo) and fact table Sales as below..

 

Dim_Geo              Fact_Sales                      Dim_Contact

Geo_Key              Geo_Key                 Contact_Key

Region                 Sales                       ContactName

District

Territory

 

Output should be like this..

 

Query1:

ContactName         Region        District            Territory        Sales

Contact1                   R1               D1                   T1                 100

Contact1                   R1               D1                   T2                  100

Total                                                                                          $200

 

Query2:

ContactName

 

 

 

Hi Stachu,


Thanks for your reply

 

I have my dimension tables (Contact and Geo) and fact table Sales as below..

 

Dim_Geo              Fact_Sales                      Dim_Contact

Geo_Key              Geo_Key                 Contact_Key

Region                 Sales                       ContactName

District

Territory

 

Output should be like this..

 

Query1:

ContactName         Region        District            Territory        Sales

Contact1                   R1               D1                   T1                 100

Contact1                   R1               D1                   T2                  100

Total                                                                                          $200

 

Query2:

ContactName      Region     District       Sales

Contact1                 R1            D1            100

Total                                                       $100

 

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...

 

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.

 

Sreeni

Community Support
Community Support

@jaladurgam,

 

You may try adding multiple measures instead.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors