Forum Discussion
Financial calculations using DAX based on GL Data
Model :
PBI_GLDetail is the GL detail table that includes detail level of transactions. PBI-GLChartofAccounts includes all the accounts to which the detail transactions are posed.
I want to create following table / matrix visual :
Now, categories link beginning balance, invoiced to customers,cash collected and write offs do not exist in any table. Each of them are calculated. What is the best way to accomplish this?
For instance, beginning balance has to be calculated using multiple GL Account. And so is other categories.
Usually, dimensions becomes go in rows or columns. And facts go to values. But here categories do not exist as dimensions. Is there a way to calculate this on fly using DAX?
3 Replies
this can be achived in following manner-
If all above are separate measures then use the feild parameter to create new table for the names of those measures and put that new column in the metrics and drag and drop the calculated measures in Values field.
to create new table using feild parameters:
New Parameter>Feilds>
add your 4 measures in Add and reorder feilds and click ok.Thanks for the reply vaibhavkale570.
I have tried following your steps. But this creates a field parameters and that eventually creates a column. I want the fields in field parameters as a rows. Since this is financial reporting, I need vertical calculations.
IF you have only four calculations as shown above, you can create measures and then use those measures in feilds parameter. this way you will be able to use feilds as rows in your feilds parameter .
you can use the SWITCH and selectedvalue DAX function for that .
it's bit difficult for me to write the dax without the samplec data.
