Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
@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.
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.
@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
@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.
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! 🙂
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |