Summation by category when duplicate values exist

[Anonymous]

The value of Drums for Acme is 200,000. However, the value is repeated. How do I retain the ability to, in a chart, to drill down into Product by year/month summarizing the unique value per product/company.

For instance: I would like the 3 Products ( Drums / Piano / Guitar) as the legend, and show that during July 2019, Piano sold a total of 227,670. Drums for 200k and Guitar for 409,859.

How would I group by Region/country in another graph? I can't quite get my head wrapped around summarize, but I believe that is probably where the solution lies.

Date	Product	Value	Company	Country	Region
Jul-2019	Drums	200,000	ACME	USA	AMERICAS
Aug-2019	Drums	200,000	ACME	USA	AMERICAS
Sep-2019	Drums	200,000	ACME	USA	AMERICAS
Jul-2019	Piano	100,000	ACME	USA	AMERICAS
Aug-2019	Piano	100,000	ACME	USA	AMERICAS
Sep-2019	Piano	100,000	ACME	USA	AMERICAS
Jul-2019	Guitar	409,859	BRAVO	GERMANY	EUROPE
Aug-2019	Guitar	409,859	BRAVO	GERMANY	EUROPE
Sep-2019	Guitar	409,859	BRAVO	GERMANY	EUROPE
Jul-2019	Piano	127,670	BRAVO	GERMANY	EUROPE
Aug-2019	Piano	127,670	BRAVO	GERMANY	EUROPE
Sep-2019	Piano	127,670	BRAVO	GERMANY	EUROPE

[v-yuta-msft]

@Anonymous ,

In your senario, you need add Country/Region and Company/Date as X-axis. After drill expanding all down one level, you will achieve a stacked bar chart like pattern below:

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

The drilldown isn't where I'm struggling. It's that the value of the products has been repeated for every month. Customer Acme only bought 1 set of drums for 200,000, but it is repeated for every month in the year for which the transaction occured. I want to look at the total sales by product, but only with the distinct values per product/customer.

[v-yuta-msft]

@Anonymous ,

If you have solved the issue, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[v-yuta-msft]

@Anonymous ,

I'm afraid this can't be achieved by visual directly because the data will be aggregated automatically in the visual. But there's a workaround, suppose you are using sum, you need to create new calculate columns like logic below:

Result =
CALCULATE (
    SUM ( Table[Sales] ),
    ALLEXCEPT ( Table, Table[Customer], Table[Product] )
)
    / CALCULATE (
        COUNTROWS ( Table ),
        ALLEXCEPT ( Table, Table[Customer], Table[Product] )
    )

Community Support Team _ Jimmy Tao

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

[JarroVGIT]

You can add 'layers' in your charts, like this:

If you click on the splitted arrows in the right top corner of the visual, you drill down a layer (combined). 

Kind regards

Djerro123

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

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂