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

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

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

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors