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

Use two different filters in a single mesure

Hello everyone,

 

I have a question regarding the creation of a new measure (which can be named A) by using an other measure (B) that has been already  created. The measure B represents the whole marketshare of a product.

I also have two filters, one for the sellers and another for their zones. Each zone represents an area where the product is sold and it is made of several sellers.

 

A = B(filter seller chosen by the user) / B(filter zone of the seller chosen by the user)

 

or

 

A = the marketshare of the seller compared to his zone.

 

The request containing the market share data is different from the one containing the columns used for the filter. They are linked.

 

Do you know how it can be fixed ?

 

Thank you,

 

6 REPLIES 6
Highlighted
Super User V
Super User V

Re: Use two different filters in a single mesure

Hi @Rem,

 

You information is very absctract however you have to make the measures to  calculate over your filters something like this:

 

A =
VAR Selected_Seller =
    MAX ( Table[Seller] )
VAR Selected_Zone =
    MAX ( Table[Zone] )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( Table[Column] ); Table[Seller] = Selected_Seller );
        CALCULATE ( SUM ( Table[Column] ); Table[Zone] = Selected_Zone )
    )

Where I have SUM(Table[Column]) you can use your measure.

 

Not sure if this helps in anything but without data and expected result is hard to give you a more exact response.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Use two different filters in a single mesure

Hi @MFelix,

 

Thank you for the quick answer.

 

Measure B =  products which have been sold in one year in France. = They are in a column in my table 2

Filter 1 : we can choose seller 1, seller 2.... = sellers present in a column in my table 1 = I put it in the visu as a filter

Filter 2 : zone 2, zone 2,... = a column too in table 1 too

Measure A = B filtered by the seller I have chosen / B filtered by the zone I have chosen

 

I can give you an example :

 

B = 100 000 products sold

Filter 1 = seller 1

Filter 2 = zone 3

 

A = 2000 (the products sold by the seller) / 10 000 (the products sold into the zone 3)

Highlighted
Super User V
Super User V

Re: Use two different filters in a single mesure

Hi @Rem,

 

The formula is similar to what I used in the previous post except where you have sum you should use the B measure.

 

Sorry for insisting on this but your information is too generic to help you better.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Use two different filters in a single mesure

Hi @MFelix,

 

Thank you for your answers,

 

Let's see how it works with a concret example :

 

Here is the Measure B which is created thanks to a function. The result is 100 000 products sold in a year in a whole country.

As you can see, I also have two filters which determine a zone and a seller. Several sellers belong to a single zone.

 

 

PW BI 1.PNG

 

If I'm selecting a zone, I have the number of products which have been sold in it.

Here we can see 5000 products for the 5.

 

PW BI zone.PNG

 

 

If I'm selecting a seller (represented by a number ex : 1401 = Georges) , I have the number of products which have been sold by him.

Here we can see 600 products for the seller 81.

 

PW BI CRE.PNG

Now I would like to divide in a new measure (A) the result of the third screen by the result of the second. => the number of product sold by a seller compared to his zone.

But the measure A must works with the other zones and sellers.

It is more clear?

 

Thank you for your time,

Regards,

Rem

 

Highlighted
Frequent Visitor

Re: Use two different filters in a single mesure

Hi @MFelix,

 

Do you better understand with these new informations?

 

Best Regards,

 

Highlighted
Super User V
Super User V

Re: Use two different filters in a single mesure

Hi @Rem,

 

You should use something similar to this

 

 

A =
VAR Selected_Seller =
    MAX ( Table[Seller] )
VAR Selected_Zone =
    MAX ( Table[Zone] )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( Table[Column] );Table[Zone] = Selected_Zone);
        CALCULATE ( SUM ( Table[Column] ); Table[Zone] =  Table[Seller] = Selected_Seller  && Selected_Zone )
    )

 

This formula will give in the first part the selected zone quantitys and in the second the seller and zone you have selected.

 

Replace Table[Column] by the measure you have created.

 

Regards.

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors