Power BI Desktop Output to Analyze in Excel, and Power BI Pagentated Report Builder

[JaclynPugh2022]

Hi,

I am trying to view the results of my report in excel pivot table.  I have also tried using Power BI Report Builder - Paginated reports, and I keep receiving the same errors no matter how small my pivot table report is.  I am trying to view one and a half years worth of data, on a small segment filtered on category 1.   ---Does anyone have any suggestion on how to get reporting to work ?  I have Power BI Premium per User, and I cannot share any of my report information ouside of Power BI Desktop

This is my measure that will not populate:    

PPV If stmnt = IF(

  SELECTEDVALUE('PPO Open Conf and Rec'[Past or Future]) = "Past",

  [STD PPV in ths],

  [PPV Forecast in ths]

)

[JaclynPugh2022]

Power BI Admin - No I did not get what I need.  I'm not going to buy a coproarte capacity level subscription, as this is what it comes down to.  I have minimized size where possbile, and after creating running totals my database now requires upgrade from premium per user to premium per capacity in order to handle queries bigger than 10240MG sounds like a scam. I have resorted to going back to excel and just using power query to at least get some work done. 

[GilbertQ]

Hi @JaclynPugh2022 

I would suggest looking at your measure and looking to make it more efficient.

Some suggestions are to make sure that your model uses a star schema. 

Next is to make sure that your measure uses all the best practices advice via www.sqlbi.com to ensure that it does not consume a lot of memory when doing the DAX.

You can also use DAX studio to see where the time is being spent and potential issues.

[JaclynPugh2022]

What do you suggest tweeking on this measure to make it more effecient?

PPV If stmnt = IF(

  SELECTEDVALUE('PPO Open Conf and Rec'[Past or Future]) = "Past",

  [STD PPV in ths],

  [PPV Forecast in ths]

)

[GilbertQ]

Hi @JaclynPugh2022 

If you can please have a look at the measures below:

[STD PPV in ths]

[PPV Forecast in ths]

Also how large is your table and column 'PPO Open Conf and Rec'[Past or Future]?

If that is very big it might also add to the memory constraints.

[JaclynPugh2022]

[STD PPV in ths]

The first measure displays the result into thousands format: 

STD PPV in ths = DIVIDE([Consolidated STD MTD PPV],1000)

The meaure within this calculates variance by multiplying the quantity by the change in price:

Consolidated STD MTD PPV = SUMX(VALUES('PPO Open Conf and Rec'[Yr and Item]),CALCULATE([MTD Weighted Price minus Weighted Avg STD MTD Price] * [Running Total Qty]))

[PPV Forecast in ths] 

This displays the PPV Forecast formula in thousands format:  

PPV Forecast in ths = DIVIDE([PPV Forecast],1000)

which contains this measure:   

PPV Forecast = [Last Price minus Weighted Avg STD MTD Price] * [Running Total Qty]

Table size, i've narrowed it down to 2021, 2022 and 2023 (3 months only) forecast.