Forum Discussion

Gazi_Sohan's avatar
Gazi_Sohan
Icon for Helper I rankHelper I
2 years ago
Solved

Need Help with Calculate

Hi guys, I am trying to calculate Net Income between 2 accounts (account numbers between 400000 and 957090), however I want the account number to come from the columns instead of hard coded. I tried ...
  • Gazi_Sohan's avatar
    Gazi_Sohan
    2 years ago

    Hey Anonymous 
    Thanks for your solution. Though it's working on the sample file but it didn't work out in our main file.

    I have tried my own way by using a pretty simple approach i.e. using selectedvalue function and it's working perfect in the measure and made it dynamic - 



    I have applied the same concept in the sample POC file that I provided for you (attached) - 


    and it's also working fine in the sample file - 

    But anyway, thanks for your help. I really appreciate it. You are a very talneted guy I must say.
    Hats off ðŸ’¥


    .pbix file with my dynamic measure - 
    https://drive.google.com/file/d/1jRhL9q_ytVP4OxSyjntFliLqdm_IXynP/view?usp=sharing

Gazi_Sohan's avatar
Gazi_Sohan
Icon for Helper I rankHelper I
2 years ago

Hi Anonymous 
Let me simplify it for you. Forget about Net Income.

I have a created the DAX measures in which there is a filtering on Account ID column of COADetails table. 

Now the issue is that, in the measure, when I write the Account ID numbers as hardcoded, the measure works, but when I write those dynamically, it doesn't work. To avoid confusion, I have created the same DAX measure twice, one being hardcoded and the other being dynamic - 

I am providing the necessary files i.e. the datasource (excel file) and the Power BI report which you will get in the below link - 
https://drive.google.com/drive/folders/1xDiFgNOm_OpwcqfcgNhInF1homob_suP?usp=sharing


Can you please tell me what am I doing wrong ?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Gazi_Sohan ,

 

We can create two calculated columns on table COA Details.

Column 1 = MID([Operator Between Totaling], 1, SEARCH("..", [Operator Between Totaling], , 1) - 1) 
Column 2 = MID([Operator Between Totaling], SEARCH("..", [Operator Between Totaling], , 1) + 2, LEN([Operator Between Totaling]))

Then we can update the measure.

Dynamic Accounts = 
var selectedrecord = SELECTEDVALUE('COA Summary'[Account ID])
var _Description=SELECTEDVALUE('COA Summary'[Account Description])
var _a=CALCULATE(MAX('COA Details'[Column 1]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _b=CALCULATE(MAX('COA Details'[Column 2]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _c=CALCULATE(MAX('COA Details'[Column 1]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _d=CALCULATE(MAX('COA Details'[Column 2]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
RETURN
SWITCH(
    selectedrecord,
    106,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Account ID] >= CONVERT(_a,INTEGER)
        && 'COA Details'[Account ID] <= CONVERT(_b,INTEGER)
    ),
    206,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Account ID] >= CONVERT(_c,INTEGER)
        && 'COA Details'[Account ID] <= CONVERT(_d,INTEGER)
    ),
    CALCULATE(
        SUM(Transactions[Amount]),
        'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]
    )
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.