Power BI chart with slicers and two bar when the second bar have modificated filter

Question

I have small problem with chart in PowerBi.

I created 4 tables and set up connections between them:

Year    
-----------
Id  | Value    
-----------
Y_19| 2019
Y_20| 2020
Y_21| 2021

Month
-----------
Id  | Value
-----------
M_Jan | January
M_Feb | February
M_Mar | March
M_Apr | April

Tax
--------------------------
Id       | Value | YearId
--------------------------
Tax_Y_19 | 0.9   | Y_19
Tax_Y_20 | 0.1   | Y_20
Tax_Y_21 | 0.4   | Y_21

Amount
-------------------------------
Id   | MonthId | YearId | Value
A_01 | M_Jan   | Y_19   | 10000
A_02 | M_Feb   | Y_19   | 2000
A_03 | M_Mar   | Y_19   | 4000
A_04 | M_Jan   | Y_20   | 5000
A_05 | M_Feb   | Y_20   | 70000
A_06 | M_Mar   | Y_20   | 10000

Then I created slicers where I can choose year (connected column Year[Value]) and created chart with Amount[Value] (y axis), Month[Value] (x axis).

Everything works fine for me, thanks to the relationship of the tables, everything is filtered by year.

But I would like to show the values multiplied by tax if I can somehow make the chart [Tax] * [Amount] - both tables are already filtered by the slicers and when I get it,

I would like to show the bar for Year-1 data on this chart, but I already have a global filter by slicers for a specific year, how could I do it.

I tried to use measure in which I get data from a slicers (Year) using selectedvalue and change the filter on the tables (Tax and Amount) with keepfilter, but measure returns an error that I can't return the table

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-16T16:22:19

Your question isn't entirely clear but if your Year table has a relationship with Tax and Amount, then I'm guessing you're after something like this as a measure:

TaxedAmount = 
SUMX ( Year, RELATED ( Tax[Value] ) * CALCULATE ( SUM ( Amount[Value] ) ) )

Power BI chart with slicers and two bar when the second bar have modificated filter

1 Answers