Forum Discussion

David_1970's avatar
David_1970
Frequent Visitor
9 years ago
Solved

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 Revenu...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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]
    )
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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]
)
David_1970's avatar
David_1970
Frequent Visitor
9 years ago

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.