wrong total

[jaweher899]

I've the following report https://1drv.ms/u/s!Amd7BXzYs7AVlx7RRKcucc1TBZ1F?e=v3mDNM

I've an issue with the total, how to correct it please?

[bolfri · Accepted solution]

Another solution (if you're expecting a lot of combinations).

Your unpivoting should include Origination Credit USD. Then a table looks like this. You can also add Correct ColumNames check to make it easier:

Correct ColumnNames = 
AND(
    RELATED('Table'[ColumnNames])=[ColumnNames];
    RELATED('Table'[Originator])=Factsales_copy[Originator]
)

Then the formula looks like this:

Correct totals 2 = 
SUMX(
    FILTER(Factsales_copy;Factsales_copy[Correct ColumnNames]=TRUE);
    Factsales_copy[Value] * Factsales_copy[Origination Credit USD]
    )

[bolfri · Accepted solution]

Another solution (if you're expecting a lot of combinations).

Your unpivoting should include Origination Credit USD. Then a table looks like this. You can also add Correct ColumNames check to make it easier:

Correct ColumnNames = 
AND(
    RELATED('Table'[ColumnNames])=[ColumnNames];
    RELATED('Table'[Originator])=Factsales_copy[Originator]
)

Then the formula looks like this:

Correct totals 2 = 
SUMX(
    FILTER(Factsales_copy;Factsales_copy[Correct ColumnNames]=TRUE);
    Factsales_copy[Value] * Factsales_copy[Origination Credit USD]
    )

[jaweher899]

Thank you for your reply, in the real project, the origination credit usd is a compex measure from different tables. 

[bolfri · Accepted solution]

Ok, but in the sample data that you've provided [Origination Credit USD] is in the table. Anyway - this will still works fine. 🙂 Just change the refference from column to measure that you have.

[bolfri]

The question is: how many combinations you have in your data? If it's like in this sample onlr 4 columns then I suggest not to duplicate factsales and use one measure:

Correct totals = 
SUMX(
    Factsales;
    SWITCH(RELATED('Table'[ColumnNames]);
        "CF_ZK";[CF_ZK];
        "CF_SH";[CF_SH];
        "CF_GOS";[CF_GOS];
        "CF_EFY";[CF_EFY])*Factsales[Origination Credit USD])

Tell me if you're expecting a lot of columns with values and originators - then I will prepare different solution for you.