FIltering but no quite filtering

[Stelian]

Hello everybody,

I need to create a report in power bi and I have this table:

level 1	level 2	Amount
M1	C1	10
M1	C2	20
M1	C3	30
M1	M1	5

in level 1 a market, in level 2 companies contained in the level 1 market, column 3 values. I am using first 2 columns (level 1 and level 2) as distinct filters.

I need this:

- when I select level 1 M1 without selecting nothing on level 2 , I need to get level 2 M1 value - 5;

- after selecting level 1 M1, when I select level 2 C1 I need to have C1 value - 10,

- if I select C1 and C2 , I want to get value 30 (C1+C2).

Can you help me with this please?

[Stelian]

Thank you.

[punitkhatri]

Happy to help..!!

[punitkhatri · Accepted solution]

Hii @Stelian 

I made the following measure, and got the results that are there in the screenshots attached.
Please try it and let me know whether your problem is solved or not.
Measure Formula = 

Slicer =

VAR Markets =

    SELECTEDVALUE ( Marketcompany[Market] )

VAR Companies =

    SELECTEDVALUE ( Marketcompany[Company] )

VAR Amt =

    CALCULATE ( SUM ( Marketcompany[Amount] ), ALL ( Marketcompany[Market] ) )

VAR AmtMarket =

    CALCULATE ( SUM ( Marketcompany[Amount] ), Marketcompany[Company] = Markets )

RETURN

    SWITCH (

        TRUE (),

        AND (

            ISFILTERED ( 'Marketcompany'[Market] ),

            ISFILTERED ( Marketcompany[Company] )

        ), Amt,

        ISFILTERED ( Marketcompany[Market] ), AmtMarket,

        Amt

    )

[Stelian]

Thank you so much.

[amitchandak]

@Stelian , That should happen with 2 separate slicers. default power bi feature, can you share the issue screenshot

https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-deskt...

[Stelian]

The company structure:

market	company
M1	C1
M1	C2
M1	C3
M1	M1
M2	C4
M2	C5
M2	C6
M2	M2

The figures:

company	Amount
C1	10
C2	20
C3	30
M1	5
C4	23
C5	54
C6	65
M2	12

The filters:

market		company
M1		C1
M2		C2
		C3
		M1
		C4
		C5
		C6
		M2

When I select only market M1 I need to get only M1 value from company level: 5.

When I select market market M1 and company C1, i need to get only C1 value 10.

When I select market market M1 and companies C1 and C2, i need to get C1+C2 value 30.

I've created new tables one for market and one for company and connected with first table, I've tried all types of connections, hoping that when I select only market M1 with no selections for companies I will get the value for second level M1 but I allways get the sum of M1, C1, C2 and C3.