Forum Discussion

mq2020's avatar
mq2020
Icon for Helper III rankHelper III
6 years ago
Solved

Create calculated column from a calculation in a different table

Hi!   I have a table (Table 1) with organisations and another table (Table 2) with users per organisation and whether they are claimin or not. I want to add a calculated column in Table 1 (in blue...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi mq2020 ,

    Please try to create the following measure to get the flag of every organization with claim:

    Org Claiming YN = 
VAR sumofClaimU =
    CALCULATE ( DISTINCTCOUNT ( 'User'[User] ), 'User'[User Claimed Y/N] = "y" )
RETURN
    IF ( sumofClaimU > 0, "Y", "N" )

    Best Regards

    Rena

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

mq2020 assuming you have a relationship between these two tables on Organisation column, just add a measure and in table visual , drop organization and this new measure.

 

Measure = 
CALCULATE( COUNTROWS ( Table2 ), Table2[User Claiming] = "Y" )

 

mq2020's avatar
mq2020
Icon for Helper III rankHelper III
6 years ago

Hi!

I do have a relationship of 1 to Many.

What I am trying to do is display a line chart with av.sales for 2 groups (Organisations with users claiming vs Organisations with users not claiming).

The sales are in a 3rd table with has a relationship of Many to 1 with Table 1 (Organisations) so I end up with:

Sales (Many to 1) -> Organisations (1 to Many) -> Users

When i put together info from the sales table and the Users table it is not working, hence, I figured, it may be easier if i add the column I need in the Organisations table (i.e if that organisation has any user claiming)

 

Thanks,

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi mq2020 ,

    Could you please provide some sample data in table "Sales"(exclude sensitive data)? What's your expected result? What you want is to get the average of sales? If yes, then whether it need to calculate based on certain conditions?

    What I am trying to do is display a line chart with av.sales for 2 groups (Organisations with users claiming vs Organisations with users not claiming).

    Best Regards

    Rena

    • mq2020's avatar
      mq2020
      Icon for Helper III rankHelper III
      6 years ago

      Hi Anonymous 

       

      These are the tables and relationships:

       

      Sample data of the 3 tables:

       

      Sales -> 

       

      Organisations -> 

       

      Users -> 

       

      At the moment I am displaying Av/units per organisation (a measure in the model) over time (visual below).

      What I'd like to do is split this into 2 groups (organisations with users claiming vs organisations with users not claiming). 

      When I try adding this field (User Claimed Y/N) to the model (in the legend part of the visual), it's not doing what I expect.

       

       

      What I expect is the following (which I've done in excel to demonstrate)

       

       
      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        mq2020 It is a bit of confusing model, what happens if users under an organization have both yes and no, which bucket it will fall or this will never happen? Yes/No it is at a user level and you are viewing the data at org level, so you need to clarify your business rules.

         

        There are many ways to do it but it will depend on your answer above.