Re: Measure Total, again

DrillDownBI

Hi,

this is my third day struggling with measure total, time to get some help.

I have 3 columns in my matrix tabel that should be summarized row by row, and then ofcource sum up everything in the total.

Column 1 [PY Actual] is Value from my fact table, column 2 [Inflation_basic] is a value from Inflation table, this tables have dates and Items. Column 3 [Inflation Effect] is Column 1 * Column 2 and gives me correct row value but then sum Total * Total and thats were the problem is. Item and Calendar is the dim tables.

I have try summarize, summarize with sumX, hasonevalue, but no luck.

FBergamaschi

You need to go Item by Item, so that will be done at the total as well, otherwise at the total you are multiplying the two totals of the other two measures

to do this you need to iterate VALUES ( Item) like

SUMX ( VALUES ( item ), [PY ACtual] * [Inlation basic] )

Not sure you need to SUM (SUMX) or AVERAGE (AVERAGEX ) the result

Hope this helpes, to help more please provide tables or pbix file

please give kudos or mark as a solution in case

Thanks!

best

DrillDownBI

Thanks,

but no luck with VALUES, I´m dealing with different granularity from all 4 tables.

Calendar have Period "yyyy-mm"
Fs Item have Item
Salary have Item and Period and is a SCD2 table

Fact have Item and Period.

Also test crossfilter, userelationship, crossfilter direction to both, everything mess up the total.

Some report should stay in excel 🙂

I cant merge tables because Inflation will affect on PY Actuals, so 2024-06 YTD * 2025-06 Inflation.
Can´t provide pbix.

FBergamaschi

The only way to help you then is that you provide dummy data for all the tables and desired results so we can import them, otherwise it is impossible to help you