Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
a68tbird
Resolver II
Resolver II

Measure Applies Row Context When Brought into Paginated Reports

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!

2 REPLIES 2
v-rongtiep-msft
Community Support
Community Support

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

vrongtiepmsft_0-1680056919960.png

 

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.

Thanks @v-rongtiep-msft. 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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors