Forum Discussion

nagaraj007's avatar
nagaraj007
Icon for Post Patron rankPost Patron
6 years ago

Unable to display Total in Matrix

Hello All, I have added one column named TARGET to the MAIN TABLE from other TABLE using LOOKUPVALUE (formula is also shown), how ever when i put the value in table matrix i am unable to get the TOT...
Need Help
Show and Tell
nagaraj007's avatar
nagaraj007
Icon for Post Patron rankPost Patron
6 years ago

I am unable to get the result as per the below query 

Target1 = SUMX(SUMMARIZE('BSC Final','BSC Final'[strategic_objective_description],"ttarget",Actuals[Target]),[ttarget])
 
Please advice
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

nagaraj007 

 

Target1 = SUMX(SUMMARIZE('BSC Final','BSC Final'[strategic_objective_description],"ttarget",Actuals[Target]),[ttarget])

 

Is the section highlighted in red your [target] measure?

( & the table and column reference in the SUMMARIZE function (in blue) must be the same as the one used in the visual for "Strategic Objective")

 

Having said all that, It is slightly odd that you are using a LOOKUPVALUE function in this way. How is your data structured? Are the "Target" values and "Actuals from different tables? Do they have a common field (for example, "Strategic Objective")? can you set up a bridge table with a common field? etc etc

 

If you can share your data structure and table structures (hiding confidential data of course) we might be able to provide a much more efficient solution.

  • nagaraj007's avatar
    nagaraj007
    Icon for Post Patron rankPost Patron
    6 years ago

    I have 2 tables:

    from 1st table i am using Stratergic objective as common and in 2nd table custom column name (same as Stratergic objective) pulling the data using LOOKUPVALUE to get the target in the main table which is table 2.

     

    Target is not a measure, but others in the matrix are all measure. I want to show the sum of target in the matrix table which is not showing.

     

    2nd table (main table)1st table

    • dax's avatar
      dax
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi nagaraj007,

      This might be related to aggregation. I find that when I set like below, it won't sum, you could check it

       

      Best Regards,
      Zoe Zhi

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

      • nagaraj007's avatar
        nagaraj007
        Icon for Post Patron rankPost Patron
        6 years ago

        Hi Zoe,

        I did that, now the problem is Target is getting summed up based on Custom, but i want lookupvalue to take 1 item as 4440 and make others as 0. That would solve my problem, as its getting summed up of the same custom column which is wrong.

         

        Based on custom name it should take only 1 item and make others 0.

         

        Could you please help me to fix this issue, it would be of great help.