Measure Applies Row Context When Brought into Paginated Reports

[a68tbird]

Hello -

  I have a PBI report that I am using as my Paginated Report data source.  My report has the following measure:

GrossProfit = 
VAR __AdminCost = 1833.00
RETURN

SWITCH(SELECTEDVALUE(Sales[StoreName]),
    "Store1", [TotalExchangeGross] - [TotalCost] - [TotalTransFee] - __AdminCost,
    "Store2",  [TotalExchangeGross] - [TotalCost] - [TotalTransFee] - __AdminCost,
    [TotalExchangeGross] - [TotalCost] - [TotalTransFee] 
    )

The Gross Profit is calculated as Total Exchanged Gross - Total Costs - Total Transaction Fees except when the store name is equal to those two stores. In those cases an additional 1833.00 must also be subtracted. This works fine on my PowerBI report as I'm just using the measure on a card, and so there is no row context. However, when I bring this measure into my Paginated report dataset, it applies row context, and calculates that 1833.00 for each row of the dataset (where the store names are relevant). This is not what I want.

So, I tried creating a Gross Profit calculated field in my paginated report dataset, but I would need a reference to that Admin Cost value.  I created an Admin Field what was just simply 1833.00, and then tried to use that in a Gross Profit calculated field. The problem here is that you are not able to use any sort of aggregation operation (such as Min/Max, or First), and so I just end up with the value being included for every relevant row again. 

Any suggestions on how I can replicate this calculation for my paginated report?

Thanks!

[Anonymous]

Hi @a68tbird ,

Please have a try.

Add a measure use isinscope.

Measure = var _1=SUMMARIZE(Sales,Sales[StoreName],"aaa",[GrossProfit])
return
IF(ISINSCOPE(Sales[StoreName]),[GrossProfit],SUMX(_1,[aaa]))

Solved: Change row context in paginated reports? - Microsoft Power BI Community

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[a68tbird]

Thanks @Anonymous. I'm not familiar with that ISINSCOPE function. I adapted the measure for my report, and it does what is expected in my PowerBI report, but unfortunately bringing this measure into my Paginated report dataset once again sums up that calculation for each row. I wouldn't really know how to share the entire PBIX and RDL in such a way that all connections still work, while also protecting my private data, so in the end I've created a workaround (if somewhat inelegant) by calling ReportItems!Fields in the paginated report and creating calculations based on those fields to recreate this Gross Profit measure. 

I don't know if the DAX query used to populate the paginated report dataset would be of any help, but here it is anyway:

EVALUATE
SUMMARIZECOLUMNS (
    'Sales'[RevenueStream],
    'Sales'[OrderDate],
    'Sales'[ProductName],
    'Sales'[Quantity],
    'Sales'[ArtistShare],
    'Sales'[StoreName],
    'Sales'[BaseCurrency],
    'Sales'[AdminCost],
    'Sales'[SalesPeriod],
    RSCUSTOMDAXFILTER (
        @SalesSalesPeriod,
        EqualToCondition,
        [Sales].[SalesPeriod],
        String
    ),
    RSCUSTOMDAXFILTER (
        @SalesStoreName,
        EqualToCondition,
        [Sales].[StoreName],
        String
    ),
    "TotalArtistShare", [TotalArtistShare],
    "TotalQuantity", [TotalQuantity],
    "TotalExchangeGross", [TotalExchangeGross],
    "TotalCost", [TotalCost],
    "GrossProfit", [GrossProfit],
    "TotalTransFee", [TotalTransFee]
 
)

 Thanks.