Forum Discussion

SaraFtg's avatar
SaraFtg
Frequent Visitor
5 years ago
Solved

Calculated column changes value in report

Hi,

 

First, English is not my mother tounge, so I hope I make myself understood :).

 

I'm quite new att Power BI and I'm struggeling with a calculated column and the outcoming result in my report.

So, my company want's to follow up on how "old" a member is when they pay there membership fee (number of years as a member), and I have one column that calculates that:

 

 

MemberYears =
Var vContractMemberFrom = RELATED(Customer[Contract Member From])
Var vMinPeriodStartingDate = MINX(RELATEDTABLE(Sales), Sales[Member Period Starting Date])
Var vMemberYears =
IF(
ISBLANK(vContractMemberFrom),
-1,
ROUNDDOWN(YEARFRAC(vContractMemberFrom, vMinPeriodStartingDate, 1), 0)
)
RETURN
IF(NOT ISBLANK(vMinPeriodStartingDate), vMemberYears)

 

Why we have both "Missing" and "(Blank)" is because an invoice may refer to either a membership fee or other sales. 

 

Second, I'm grouping these values with SWITCH in an other column:

 

 

MemberYears (group) =
SWITCH(
TRUE(),
'Sales Headers'[MemberYears] = -1, "Missing",
ISBLANK('Sales Headers'[MemberYears]), "(Blank)",
'Sales Headers'[MemberYears] = 0, "0",
'Sales Headers'[MemberYears] = 1, "1",
'Sales Headers'[MemberYears] = 2, "2",
'Sales Headers'[MemberYears] = 3, "3",
'Sales Headers'[MemberYears] = 4, "4",
'Sales Headers'[MemberYears] = 5, "5",
'Sales Headers'[MemberYears] >= 6 && 'Sales Headers'[MemberYears] < 11, "6-10",
'Sales Headers'[MemberYears] >= 11 && 'Sales Headers'[MemberYears] < 21, "11-20",
'Sales Headers'[MemberYears] >= 21 && 'Sales Headers'[MemberYears] < 51, "21-50",
'Sales Headers'[MemberYears] >= 51 && 'Sales Headers'[MemberYears] < 1000, "50+"
)
 
I get the expected result in my datamodel
 

 

but in my report, all the items with value "-1", that should be grouped as "Missing", is converted to "50+" Why is that?

 

I've tried to add && <> -1 on the last row, but that doesn't work either. How is it possible that the datamodel and the report shows different values? Any one who has an idea or solution?

 

Thanks in advance!

 

 

Need Help
  • SaraFtg's avatar
    SaraFtg
    5 years ago

    Hi selimovd,

     

    After some more digging I found that empty cells in date column in my Customer table returned 30/12/1899, so when I calculated MemberYear the result became 120 years.

     

    Instead of IF(ISBLANK(vContractMemberFrom),-1,... I tried IF(vContractMemberFrom = 0,-1,... and now it seems to work fine.

     
    Thanks for taking your time to respond to my issue!

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey SaraFtg ,

     

    can you check if Document No 765663 has multiple rows in your data?

    If you chose MAX as aggregation for MemberYears (group) it could be that it would show the MAX of the rows and if that Document No has multiple rows that would explain it.

     

    Also try to set the aggregation in the visual to "Don't summarize":

     

    Like this all rows should be shown for sure.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • SaraFtg's avatar
      SaraFtg
      Frequent Visitor
      5 years ago

      Hi selimovd,

       

      Thanks for you reply!

       

      Document No appears in three different tables, and on several rows in two of them, but not in the table where this calculation is made. 

       

      The aggregation is set to Don't summarize, these are the options I have

       

       

      Best regards,

      Sara

       

    • SaraFtg's avatar
      SaraFtg
      Frequent Visitor
      5 years ago

      Hm, when adding the MembertYear-column (also set to Don't summarize) to my visual the problem seems to be that -1 is set to 120 (resulting in that it's grouped as "50+"). So - question is why that is happening?

       

       

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey SaraFtg ,

         

        can you share the file? It would be a lot easier to take a look at the file itself.

         

        Best regards

        Denis

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi SaraFtg ,

     

    After reviewing your question, it seems that it has been resolved. You can mark the correct response as the standard answer to help the other members find it more quickly.

     

    If there is still confusion, you can describe your problem in detail and provide relevant data and information. I will answer you as soon as possible.


    Best Regards,
    Henry