Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rem
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
MFelix
Super User
Super User

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



Rem
Frequent Visitor

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)

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



Rem
Frequent Visitor

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

 

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



Rem
Frequent Visitor

Hi @MFelix,

 

Do you better understand with these new informations?

 

Best Regards,

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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