How make a measure(total revenue) adding multiple general ledger account No.

[Anonymous]

Hi PBIXer,

I want create a measure ( Total Revenue ) using a general ledger account No.(300001,3000002,300005) taking sum of all the mentioned  G/L account no. amount.

I'm trying to go in the row level to add mentioned account No. to achieve this measure but not getting it .

Attaching the Screenshot to simplify the Understanding,

A swift reply is appreciable.

Thank you in advance.

[Syk]

To get the sum row by row you can use sumx like this

Total Revenue = SUMX(Table,Table[Amount])

[OwenAuger · Accepted solution]

Hi @Anonymous 

As a starting point, I would try a measure like this:

Total Revenue =
CALCULATE (
    SUM ( YourTable[Amount] ),
    YourTable[G/L Account No.] IN { 300001, 3000002, 300005 }
)

Account Numbers may need to be placed in double quotes if the column is of type text.

Also, I would generally advise having a separate G/L dimension table related to your fact table, and apply filters to that table.

Regards,

Owen

[Anonymous]

Hi Owen,

Can you please explain about the suggestion you mentioned for the having a separate G/L dimension table related to your fact table.

(Also, I would generally advise having a separate G/L dimension table related to your fact table, and apply filters to that table.)

Can you make a simple demo PBIX and explain.

But Thank you achieve 90% of my doubt.