Reply
Highlighted
Frequent Visitor
Posts: 10
Registered: ‎06-27-2017
Accepted Solution

Creating measure

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

ProjectTypeAmount
158894Gross Revenue2000
158894Gross Revenue1500
158894Gross Revenue1000
158894Disbursement-700
158894Disbursement-300

Accepted Solutions
Community Support Team
Posts: 5,664
Registered: ‎09-21-2016

Re: Creating measure

Hi @David_1970,

 

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

1.PNG

 

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]
)
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Super User
Posts: 1,499
Registered: ‎07-27-2016

Re: Creating measure

@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)

 

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Community Support Team
Posts: 5,664
Registered: ‎09-21-2016

Re: Creating measure

Hi @David_1970,

 

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

1.PNG

 

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]
)
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 10
Registered: ‎06-27-2017

Re: Creating measure

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.