Forum Discussion
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
| Project | Type | Amount |
| 158894 | Gross Revenue | 2000 |
| 158894 | Gross Revenue | 1500 |
| 158894 | Gross Revenue | 1000 |
| 158894 | Disbursement | -700 |
| 158894 | Disbursement | -300 |
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
3 Replies
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)
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 GuHi 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.
