Forum Discussion
How make a measure(total revenue) adding multiple general ledger account No.
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.
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
3 Replies
- Syk
Resident Rockstar
To get the sum row by row you can use sumx like this
Total Revenue = SUMX(Table,Table[Amount]) - OwenAuger
Super User
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
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.
