Calculated measure works in Power Pivot but not Power BI

[Stolajo1]

Hi, I have created an Income statement with some calculated measures. to get a proper income statement with sub totals like Gross Profit and Operating Profit

I found a youtube clip to create incomes statement and it worked in Power Pivot for Excel. https://www.youtube.com/watch?v=ojHZkWkEY7Q

The wanted outcome is this;

Net Sales

Cogs

Gross Profit

Operating expenses

Operating Profit

It works in Power BI Desktop as well, but if I add a dimension to the matrix columns the Actual is not working for the rows Gross Profit and Operating Profit. In Power Pivot there is no problem to see all Income statement rows by Product Area...

This are the measures that create the Income Statement;

ActualReversed = SUM(FactGLTotals[AMOUNT])*-1

ActualRunning = IF(HASONEFILTER(DimHeader[INCOME STATEMENT]);CALCULATE([ActualReversed];ALLNOBLANKROW(DimHeader);DimHeader[ORDER]<VALUES(DimHeader[ORDER]));BLANK())

Actual = SWITCH([HeaderCalcType];1;[ActualReversed];2;[ActualRunning])

Is this a limitation in Power BI or have i done anything wrong?  Any Ideas?

[v-ljerr-msft]

@Stolajo1

After studying the pbix file and the sample data you provided, I found that the DimAccounts table just contains data of OE, CO, and NS, which means there is no any data for GP(Gross Profit) and OP(Operating Profit) in DimAccounts table. That's why Gross Profit and Operating Profit is only calculated for the total and not individual markets in the report, because there is no data for them to show.

So the logic also works in Power BI, but the data source itself is not good. If you use the same data source for Power BI and Power Pivot, then the result should be the same.

Regards

[Stolajo1]

I have the same source data in PowerPivot and there it works. I have uploaded the PowerPivot model to One Drive folder as well.. If you want to check it...

Gross Profit is a calculated member, ie a sub total of Net Sales - Cost of Goods Sold. That is why i have to calculate those members/sub totals (Gross Profit and Operating Profit).

I use the measure ActualRunning to calculate the subtotals and then use a switch in measure Actual to either get data from FactGLTotals table or subtotals from the measure ActualRunning...

It seems to be a problem with the calculation of RunningActual when more dimensions are added to the matrix, PowerPivot can handle this, but is it a bug in Power BI or is that Logic only for PowerPivot, maybe there is a smarter way to get theese figures in PowerBI. 

Sounds strange since DAX should be DAX... I am just a finance guy and no BI wizz, I just want to get the numbers right  😉

Thanks for your help, 

[v-ljerr-msft]

@Stolajo1

---

It works in Power BI Desktop as well, but if I add a dimension to the matrix columns the Actual is not working for the rows Gross Profit and Operating Profit. In Power Pivot there is no problem to see all Income statement rows by Product Area...

---

What do you mean about "the Actual is not working for the rows Gross Profit and Operating Profit"? Do you get any error or you just get the wrong result? Could you be more precisely with that? And could you post your table structure and some sample data in your case? It's better to upload a pbix file.

Regards

[Stolajo1]

Hi JerryLi,

I think the issue is related granuality...

Sorry but I am new to this forum, how do i upload a pbix file?

/Jonas

[v-ljerr-msft]

@Stolajo1

You can upload it to onedrive or dropbox and send me the link in private message. Do mask sensitive data before uploading.

Regards