Unable to display Total in Matrix

[nagaraj007]

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 TOTAL for the column. Please advice.

[dax]

Hi nagaraj007,

I test this in my environment which works well in Matrix. In addition, you said that the sumx(summarize())  function don't work in your environment, what output did you get currently? If possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
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.

[PaulDBrown]

@nagaraj007

try

SUMX(SUMMARIZE(table_strategic objective), table [strategic objective], “target”, [target]), target)

[nagaraj007]

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]

@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]

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]

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]

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.