Forum Discussion

ivannye's avatar
ivannye
Icon for Helper I rankHelper I
6 years ago

Total not correct when I use switch

I am trying to get a total amount of my caculation using switch but it is highly tricky. My table looks like this

 

a) and b) are part of a group

22%..., 4.5%... and b)... are also part of a group. the calculation I have used are as follow:

 

SWITCH( SELECTEDVALUE([ACCNT_CODE (groups) 7]),
"22% of Bonds + RADs",[Reserves]*0.22,
"4.5% of Entry Contributions",[Reserves]*0.045,
"b) Plus other set amount",-CALCULATE(SUM(Actual]),[ACCNT_CODE]="ZTACTICAL"))
 
I am trying to get the total per subgroup and then total reserve allocation.
Can you please help me with that?

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ivannye ，

    It can't be seen what is the cause of incorrect total per subgroup from the formula you provided... Could you please provide some sample data ? And what is the expected result ? If it is possible , it is better if you can provide your PBIX file (exclude sensitive data ).

     

    Best Regards

    Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ivannye,
    Whether your problem has been resolved? If still not, could you please provide the related table structure and more sample data(exclude sensitive data). it is better if you can provide your PBIX file
     
    Best Regards
    Rena

  • ivannye's avatar
    ivannye
    Icon for Helper I rankHelper I
    6 years ago

    Hello, My issue was resolved but another one has arisen 🙂 I am trying to the calculation on all rows with the filter as condition:

    Example : February is selected

    1- count all rows where start_date is less than february and end_date is empty and multiply by the number of days in the month

    2- count all rows where start date is less than february and end date is more than 29/02 and multiply by the number of days in the month

    3- if end_date is between 01/02 and 29/02 then do datediff between the 01/02 and the end_date

    I have the following result when I do my calculation

    My calculation is 

     
    datedifference =
    var selectionstart = SELECTEDVALUE(FC_Client_Contract[Start_Date])
    var numberofday= SWITCH(MONTH(selectionstart),
    1,31,2,29,3,31,4,30,5,31,6,30,7,30,8,31,9,30,10,31,11,30,12,31)
    var option1 = CALCULATE(COUNT(FC_Client_Contract[Client_Contract_ID]),ALLEXCEPT(FC_Client_Contract,FC_Client_Contract[level_column],FC_Client_Contract[Program_ID],FC_Client_Contract[Program_Level_ID]),DATE(YEAR(selectionstart),MONTH(selectionstart),numberofday) <= FC_Client_Contract[End_Date] || ISBLANK(FC_Client_Contract[End_Date]))*numberofday

    var option2 = CALCULATE(DATEDIFF(DATE(YEAR(selectionstart),MONTH(selectionstart),1),MIN(FC_Client_Contract[End_Date]),DAY),FILTER(ALLEXCEPT(FC_Client_Contract,FC_Client_Contract[Program_ID],FC_Client_Contract[level_column]),MONTH(selectionstart)=MONTH(FC_Client_Contract[End_Date])))

    return
    option1+option2