Grand total not showing the result I was expecting

[Anonymous]

Hello guys,

I have a model that looks like this (simplified).

My goal is to dynamically calculate the amount of income (which is on the SALESFORCE table) that comes from any chosen dimensions. Both fact table and fact table agg.
For now I'm focusing on how to do it for the Fact table agg first.

We have detail and agg table by month because our gateway can't handle the amount of rows we have for each day, so we only show the detail for the last 30 days, and everything on the aggregation table.

1. Fact table: Number of impressions by day (and other dimensions)
2. Fact table agg: Number of impressions by month (and other dimensions)
   Fact table agg Measures:
   SUMImpresionesAgg = CALCULATE(SUM(fact_table_agg[totalimpressions]))
   SUMRealRevenueAgg = CALCULATE([SUMImpresionesAgg]*[RealRevenueByImpression])
3. Salesforce: Monthly orders with their revenue.

I've created a calculated table so I can have in a single table by SALESFORCE Order it's revenue and the sum of totalimpressions.
Impressions_By_SF_Orders = SUMMARIZE(DIM3;DIM3[ORDERID];SALESFORCE[order_revenue];"Impressions";SUM(FACTS_AGG[totalimpressions]))

Then I calculate the revenue for each impression as a calculated column:
RealRevenueByImpression = DIVIDE(Impressions_By_SF_Orders[order_revenue];Impressions_By_SF_Orders[Impressions];0)

So now I thought that having how much revenue I have for each impressions I could calculate what I want using SUMRealRevenueAgg, the result looks like this:

The thing is, for each row the result is correct but SUMRealRevenueAgg shows a Total amount which is not the SUM of the column but the multiplication of both SUMImpresionesAgg and RealRevenueByImpression.
What I was expecting to see there is 28281,21.

I hope I made myself clear but I'm not that good with English.

Maybe it's some basic mistake but I tried everything and still can't find what is wrong.

Thank you!

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

It seems you may create a measure like below.

Measure =
SUMX ( VALUES ( Table[Dimesion1] ), [SUMRealRevenueAgg] )

Or

Measure =
SUMX ( SUMMARIZE ( Table, Table[Dimesion1] ), [SUMRealRevenueAgg] )

Below are some similar posts for your reference,

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Desktop/Weighted-Average-from-Measures/m-p/553018#M260335

https://community.powerbi.com/t5/Desktop/Switch-DAX-rebate-based-on-cumulative-measure/m-p/514547#M240481

Regards,

Cherie

[Anonymous]

Hello Cherie,

Thanks for your response.

I'm not sure I understand those measures. The fact that you used Table[Dimension1] means that I need one measure for each dimension I want the result to show in the dashboard?

Anyway, seeing that you used SUMX I kinda unterstand what the problem is so if I add a calculated column in the Fact Table Agg like this:

cnRealRevenue = CALCULATE(SUMX(Impressions_By_SF_Orders;Impressions_By_SF_Orders[cnRealRevenueByImpression]))

And then I create a Measure like this:

Measure = SUMX(FACTS_AGG;FACTS_AGG[cnRealRevenue]*FACTS_AGG[totalimpressions])

The last measure gaves me the result I wanted finally.

I can work with that, but I would prefer to understand your solution so I could get the same result just with measures. 

Thank you!!

EDIT:

Ok, I managed to get the same result using this measure:

Measure = SUMX(FACTS_AGG;[mSUMImpresionesAgg]*[mRealRevenueByImpression])

Which I think it's the same that your solution. I hope I'm not missing something. What was confusing me it was the fact you used Dimension1 in your measure but I guess it was just an example and I wass missing the point there.

Thank you!!