Deduping data at higher levels

[jaladurgam]

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

[v-chuncz-msft]

@jaladurgam,

You may try adding multiple measures instead.

[Stachu]

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?

[jaladurgam]

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

[jaladurgam]

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

[jaladurgam]

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