Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Summation by category when duplicate values exist

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.

 

 

DateProduct Value CompanyCountryRegion
Jul-2019Drums  200,000ACMEUSAAMERICAS
Aug-2019Drums  200,000ACMEUSAAMERICAS
Sep-2019Drums  200,000ACMEUSAAMERICAS
Jul-2019Piano  100,000ACMEUSAAMERICAS
Aug-2019Piano  100,000ACMEUSAAMERICAS
Sep-2019Piano  100,000ACMEUSAAMERICAS
Jul-2019Guitar  409,859BRAVOGERMANYEUROPE
Aug-2019Guitar  409,859BRAVOGERMANYEUROPE
Sep-2019Guitar  409,859BRAVOGERMANYEUROPE
Jul-2019Piano  127,670BRAVOGERMANYEUROPE
Aug-2019Piano  127,670BRAVOGERMANYEUROPE
Sep-2019Piano  127,670BRAVOGERMANYEUROPE
Need Help
Tips and Tricks

5 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

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

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.