Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
mdaamirkhan
Post Prodigy
Post Prodigy

Ratio to parent Market share

‎07-11-2017 02:13 AM

Ratio to parent market share is a very common measure to use in any Business. I have search many times but didnt get exact solution and dax query. 

 

Will it be possible to add this in up coming update in power BI as quick measure?

 

Thanks

Aamir

Message 1 of 19
3,612 Views
0
2 ACCEPTED SOLUTIONS
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-18-2017 01:45 AM

Hi @mdaamirkhan,

 

This is something about context. I worked it out, but it can't be used generally.

test2 =
IF (
    ISFILTERED ( PFIZER_RADER_REPORTING[International Product] ),
    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
        / CALCULATE (
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
            ALL ( PFIZER_RADER_REPORTING[International Product] )
        ),
    IF (
        ISFILTERED ( PFIZER_RADER_REPORTING[Sub Market 1] ),
        SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
            / CALCULATE (
                SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Sub Market 1] )
            ),
        IF (
            ISFILTERED ( PFIZER_RADER_REPORTING[Market] ),
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
                / CALCULATE (
                    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                    ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Market] )
                ),
            1
        )
    )
)

Take the first two field as example. The context of "PIPERAC/TAZO  FRES" is "International Product", while the context of "Total" is "Sub Market 1". So the formula chooses the wrong "if" clause.

 

 

Ratio to parent Market share2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
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

Message 14 of 19
4,166 Views
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-18-2017 02:24 AM

Hi, try this one please: Ratio_Demo

 

Best Regards!

Dale

Community Support Team _ Dale
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

Message 18 of 19
4,154 Views
0
18 REPLIES 18
v-jiascu-msft
Microsoft Employee
Microsoft Employee

‎07-11-2017 10:25 PM

@mdaamirkhan

 

Hi Aamir,

 

Could you please share a sample here? Maybe we could work it out.

Maybe you could submit an idea in power-bi-ideas. 

Maybe you could try a formula like this:

ratio =
SUMX ( sales, sales[quantity] * sales[unit price] )
    / CALCULATE ( SUMX ( sales, sales[quantity] * sales[unit price] ), ALL ( sales ) )

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 2 of 19
3,546 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-12-2017 10:06 AM

I use this dax query for Ratio To Market share but its showing wrong value

 

Ratio To Market share =

IF (

ISFILTERED (PFIZER_RADER_REPORTING[International Product]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[International Product])

),

IF (

ISFILTERED (PFIZER_RADER_REPORTING[Sub Market 1]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[Sub Market 1])

),

IF (

ISFILTERED (PFIZER_RADER_REPORTING[Market]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[Market])

)

)

)

)

Message 3 of 19
3,535 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-13-2017 02:56 AM

Hi @mdaamirkhan,

 

Your formula looks great. Maybe something is wrong with the report. Here is an example. Please check it out.

1. There are three fields in your field, so take care of these three fields when applying a filter;

2. It's better one filter a time.

Please refer to this post, maybe it would help. dax-universal-formula-to-calculate-difference-to-previous-period

 

9Measure =
IF ( ISFILTERED ( 'Product'[Color] ), 1, 0 )

Ratio to parent Market share.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 4 of 19
3,524 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-13-2017 12:06 PM

didnt work 

Message 5 of 19
3,507 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to mdaamirkhan

‎07-14-2017 12:16 PM

Any update on the Ratio To parent Hierarchy level? I wrote this query below but its show wrong value.

Message 6 of 19
3,493 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-17-2017 04:45 AM

Hi @mdaamirkhan,

 

The picture you posted was corrupted. I can't see anything.

Could you please post the snapshot of the report? Based on the information here, I think the formula should work.

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 8 of 19
3,476 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-17-2017 05:16 AM

 

In the screenshot you can see Total showing 49% which is wrong it should be 100%

 

 

Capture.PNG 

 

MKTShareByMKTH =

IF (

ISFILTERED (PFIZER_RADER_REPORTING[International Product]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[International Product])

),

IF (

ISFILTERED (PFIZER_RADER_REPORTING[Sub Market 1]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[Sub Market 1])

),

IF (

ISFILTERED (PFIZER_RADER_REPORTING[Market]),

SUM (PFIZER_RADER_REPORTING[Sales Mnf])

/

CALCULATE (

SUM (PFIZER_RADER_REPORTING[Sales Mnf]),

ALL (PFIZER_RADER_REPORTING[Market])

)

)

)

)

Message 9 of 19
3,471 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-17-2017 05:57 AM

Hi @mdaamirkhan,

 

It's a trick of context. The "Total" hasn't have a filter all the time. Try this formula please. (format to percentage)

MKTShareByMKTH =
IF (
    ISFILTERED ( PFIZER_RADER_REPORTING[International Product] ),
    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
        / CALCULATE (
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
            ALL ( PFIZER_RADER_REPORTING[International Product] )
        ),
    IF (
        ISFILTERED ( PFIZER_RADER_REPORTING[Sub Market 1] ),
        SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
            / CALCULATE (
                SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                ALL ( PFIZER_RADER_REPORTING[Sub Market 1] )
            ),
        IF (
            ISFILTERED ( PFIZER_RADER_REPORTING[Market] ),
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
                / CALCULATE (
                    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                    ALL ( PFIZER_RADER_REPORTING[Market] )
                ),
            1
        )
    )
)

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 10 of 19
3,465 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-17-2017 10:14 AM

Same query I have tried before which is showing wrong value in Total as well as productwise value total.

Message 11 of 19
2,708 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-17-2017 05:18 PM

Hi @mdaamirkhan,

 

Are there any other filters or slicers?

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 12 of 19
2,701 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-17-2017 08:31 PM

Capture.PNG

 

Attributeslicers on the left side in the screenshot but didnt select anything  in the fliter or slicer see value showing wrong it should be 100%  .  I have also prepared the sample data for you link: https://drive.google.com/open?id=0BxreilNC4AvsbzhBR2hvZG5tZVE

 

 

Message 13 of 19
2,697 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-18-2017 01:45 AM

Hi @mdaamirkhan,

 

This is something about context. I worked it out, but it can't be used generally.

test2 =
IF (
    ISFILTERED ( PFIZER_RADER_REPORTING[International Product] ),
    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
        / CALCULATE (
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
            ALL ( PFIZER_RADER_REPORTING[International Product] )
        ),
    IF (
        ISFILTERED ( PFIZER_RADER_REPORTING[Sub Market 1] ),
        SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
            / CALCULATE (
                SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Sub Market 1] )
            ),
        IF (
            ISFILTERED ( PFIZER_RADER_REPORTING[Market] ),
            SUM ( PFIZER_RADER_REPORTING[Sales Mnf] )
                / CALCULATE (
                    SUM ( PFIZER_RADER_REPORTING[Sales Mnf] ),
                    ALLEXCEPT ( PFIZER_RADER_REPORTING, PFIZER_RADER_REPORTING[Market] )
                ),
            1
        )
    )
)

Take the first two field as example. The context of "PIPERAC/TAZO  FRES" is "International Product", while the context of "Total" is "Sub Market 1". So the formula chooses the wrong "if" clause.

 

 

Ratio to parent Market share2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 14 of 19
4,167 Views
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-18-2017 02:11 AM

It is possible to send me the solution file that you done this. it will be helpfu for me 

Message 15 of 19
2,673 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-18-2017 02:16 AM

Sure, please check this: https://1drv.ms/u/s!ArTqPk2pu-BkcREb4pQ_JKiq5PY

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 16 of 19
2,671 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-18-2017 02:20 AM

Its not downloading can you share with google drive

Message 17 of 19
2,668 Views
0
v-jiascu-msft
Microsoft Employee
Microsoft Employee
In response to mdaamirkhan

‎07-18-2017 02:24 AM

Hi, try this one please: Ratio_Demo

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 18 of 19
4,155 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to v-jiascu-msft

‎07-18-2017 03:06 AM

Thanks alot

Message 19 of 19
2,657 Views
0
mdaamirkhan
Post Prodigy
Post Prodigy
In response to mdaamirkhan

‎07-17-2017 02:51 AM

Any update ?

Message 7 of 19
3,486 Views
0

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All