Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

bars in visual not appearing properly

Hi, I am almost embarassed to ask this as I am sure it's a newbie question but I can't figure it out and I couldn't find a previous answer.   I am trying to put the change of market share (MS) of ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please try this measure to see if it matches the desired output.

     

    2020-18 REF = 
DIVIDE (
    SUM ( 'Total Sales 2018-21'[Sales 2020] ),
    SUMX (
        ALLSELECTED ( 'Total Sales 2018-21' ),
        'Total Sales 2018-21'[Sales 2020]
    )
)
    - DIVIDE (
        SUM ( 'Total Sales 2018-21'[Sales 2018] ),
        SUMX (
            ALLSELECTED ( 'Total Sales 2018-21' ),
            'Total Sales 2018-21'[Sales 2018]
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

Are your ERX, ORX, and PECS... all measures? How did you calculate them? Why do you need to create separate measure for each company? If you can use only one measure to calculate, then you will get the chart shown below.

 

 

Best Regards,
Winniz

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Winniz v-kkf-msft ,

Thank you for your kind reply!

Yes, there is a separate measure for every sister company (ERX, ORX and the rest).

 

This is the formula:

 

MS DEX 2020-18 REF = DIVIDE(
CALCULATE(
    SUM('Total Sales 2018-21'[Sales 2020]),'Total Sales 2018-21'[Partner]="DEX"), 
SUM('Total Sales 2018-21'[Sales 2020])) - 
DIVIDE(
    CALCULATE(
    SUM('Total Sales 2018-21'[Sales 2018]),'Total Sales 2018-21'[Partner]="DEX"), 
SUM('Total Sales 2018-21'[Sales 2018]))

 

Where "DEX" is the given company, 'Total Sales 2018-21' is the name of the query. All the other companies use the same formula, only the company names change.

 

I am using separate measures for every company because that was the only way I could think of to calculate the MS of the companies - but, of course, I am quite a newbie to PBI.

The image you included show exactly what I want, I am saluting you!

Could you please share the formula you used to get it?

 

Thank you again for your kindness and have a great day!

Attila

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Please try this measure to see if it matches the desired output.

     

    2020-18 REF = 
DIVIDE (
    SUM ( 'Total Sales 2018-21'[Sales 2020] ),
    SUMX (
        ALLSELECTED ( 'Total Sales 2018-21' ),
        'Total Sales 2018-21'[Sales 2020]
    )
)
    - DIVIDE (
        SUM ( 'Total Sales 2018-21'[Sales 2018] ),
        SUMX (
            ALLSELECTED ( 'Total Sales 2018-21' ),
            'Total Sales 2018-21'[Sales 2018]
        )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    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
      4 years ago

      Hi v-kkf-msft ,

      This is the correct formula!

      I can't tell you how glad I am!

      Thx a million for your kindness and for the opportunity for me to learn something new!

      The ALL functions have been on my study agenda, just didn't get to them yet. But now I am going to delve into them.
      Thx for your great help and have a great day!

      Attila