cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DannyPhillips Frequent Visitor
Frequent Visitor

Graphing Calculated Sums

I have data that essentially looks like this:

 

Company_LabelProduct_LabelCOUNT
John's ApplesRed Delicious1
John's ApplesHoneycrisp1
Industry TotalRed Delicious1
Industry TotalHoneycrisp1
Industry TotalRed Delicious1
Industry TotalRed Delicious1
Industry TotalHoneycrisp1
Industry TotalRed Delicious1

 

The apples that the company "John's Apples" sells get reported listed under John's Apples, but also as part of the industry total (essentially the first 4 rows are the 2 apples that John's Apples sold).  I want to create a market share chart for John's Apples, but if I create it as is, the numbers will be skewed as it will show John's Apples vs. Industry Total (2 of 😎 instead of John's Apples vs. Everyone Else (2 of 6).  In excel, I could create a simple calculated item in a PivotTable and chart that, but I can't figure out how to do it in Power BI. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Graphing Calculated Sums

Hi,

 

You could try below measure

Measure =
IF (
    MIN ( t1[Company_Label] ) = "John's Apples",
    CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ),
    COUNT ( t1[Company_Label] )
        - CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" )
)

80.PNG

Best Regards,

Zoe Zhi

 

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

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Graphing Calculated Sums

Hi DannyPhillips,

 

In your scenario, you said you want to get John's Apples vs. Everyone Else (2 of 6), what did you mean of “everyone”? Did you want to get John's Apples vs. Industry Total(2:6)? If so, you could just create measure Measure 3 = COUNT(sharechart[Product_Label]), it will return result like below

38.png

If this is not what you want, could you please inform me in details(such as your expecting output and your detailed sample data)? Then I will help you more correctly.

 

Best Regards,

Zoe Zhi

 

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

DannyPhillips Frequent Visitor
Frequent Visitor

Re: Graphing Calculated Sums

Thanks for the response, the issue is that your example table shows a grand total of 8, but the industry total is already the grand total.  What I really would like is to be able to chart the results like this:

Pie Chart.JPG 

but I don't have a "competitor" company label, I just know it is the sum of industry total minus the sum of John's Apples. I would also like a simple table like this:

 

Table.JPG

but I'm unsure of how to create that.

Community Support Team
Community Support Team

Re: Graphing Calculated Sums

Hi,

 

You could try below measure

Measure =
IF (
    MIN ( t1[Company_Label] ) = "John's Apples",
    CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" ),
    COUNT ( t1[Company_Label] )
        - CALCULATE ( COUNT ( t1[Company_Label] ), t1[Company_Label] = "John's Apples" )
)

80.PNG

Best Regards,

Zoe Zhi

 

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

View solution in original post

DannyPhillips Frequent Visitor
Frequent Visitor

Re: Graphing Calculated Sums

Thank you so much for the solution!  I changed it to the below to accomodate if the count was ever more than 1. This appears to be working.

 

Measure =

IF (

    MIN ( t1[Company_Label] ) = "John's Apples",

    CALCULATE ( SUM ( t1[COUNT] ), t1[Company_Label] = "John's Apples" ),

    SUM ( t1[COUNT] )

        - CALCULATE ( SUM ( t1[COUNT] ), t1[Company_Label] = "John's Apples" )

)

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors