Desktop

[David_1970]

Hi,

The table below show gross revenue and disbursement for a lot of projects (only one shown though).  On my report canvas I want a slicer where I can toggle between Gross Revenue and Net Revenue, where Net Revenue should be calculated as Net Revenue = Gross Revenue + Disbursement.

I imagine I should create a New Measure but since Power BI and DAX is quite new to me I can't find out how.

Any ideas?

Table1

Project	Type	Amount
158894	Gross Revenue	2000
158894	Gross Revenue	1500
158894	Gross Revenue	1000
158894	Disbursement	-700
158894	Disbursement	-300

[v-yulgu-msft · Accepted solution]

Hi @David_1970,

You need an extra table which contains only one column that will be added into slicer later. 

Create measures as below:

Gross Revenue =
CALCULATE (
    SUM ( 'revenue  table'[Amount] ),
    'revenue  table'[Type] = "Gross Revenue"
)

Net Revenue =
SUM ( 'revenue  table'[Amount] )

Revenue =
IF (
    LASTNONBLANK ( 'Slicer table'[Revenue Type], 1 ) = "Gross Revenue",
    [Gross Revenue],
    [Net Revenue]
)     

Best regards,
Yuliana Gu

[vanessafvg]

@David_1970 to toggle look at the tecnique in this video from  time 11:37

https://www.youtube.com/watch?v=Mpk9Js5jUeg

to create the measure

Net Revenue = sum(Gross Revenue) + sum(Disbursement)

[v-yulgu-msft · Accepted solution]

Hi @David_1970,

You need an extra table which contains only one column that will be added into slicer later. 

Create measures as below:

Gross Revenue =
CALCULATE (
    SUM ( 'revenue  table'[Amount] ),
    'revenue  table'[Type] = "Gross Revenue"
)

Net Revenue =
SUM ( 'revenue  table'[Amount] )

Revenue =
IF (
    LASTNONBLANK ( 'Slicer table'[Revenue Type], 1 ) = "Gross Revenue",
    [Gross Revenue],
    [Net Revenue]
)     

Best regards,
Yuliana Gu

[David_1970]

Hi and thanks!

However, I also seem to get a (blank) tick box option in my slicer on the canvas in addition to the gross revenue and net revenue tick boxes. And revenue is only shown in the card visual when the (blank) tick box is selected, otherwise no revenue is shown.

I guess I have to do some trouble shooting.

Once again, many thank's.