Forum Discussion
How to create a calculated row in a matrix?
Anonymous
Here you can see the % share in the picture is wrong. How I want is, Market's share / total share for that year. I know this I can achieve through chaging measure - Total Value = CALCULATE(SUM(Sheet5[Value]),ALL(Sheet5[Market])) -> which I do not want because Market is static here. Means I do not want to give any field name in the formula, it should calculate based on which field I am using in rows of matrix because any field can be used in Rows.
In this case, you can change the measure [total value] a little bit as below so that the [% share] can vary according to the row.
Total Value = CALCULATE(SUM(Sheet5[value]),FILTER(ALL(Sheet5),Sheet5[year]=MIN(Sheet5[year])))
But the total column of the matrix won't make sense in this case. You can hide it.
Thank you so much for your responses.
Greg_Deckler : I tried, but it is giving me total (irrespective of year) so it is wrong. It should be divided by the total of that particular year.
Baskar: It is giving me same percentage value which I have attached. So doesn't help.
ankitpatira: Thanks for the detailed answer but in my actual data, I have around 100 years and many rows and columns. So I could not change this to pivot column, I need to do through DAX formulas.
Eric_Zhang: Thanks for the answer, but my users want to see total column also. So it doesn't help me.
samdthompson: I tried, doesn't help.
Any other solution?
Regards,
Niket Talati
Hi,
in dax i cant find how to pick single cell value.
if we can pick like this from DAX we can do, if not we cannot do .
Dax fully based on table and column level from table.
upto my knowledge used the axisting current DAX function we cant do , sorry :smileysad::smileysad:
Eric_Zhang : My users want to have only one DAX for all scenarios (more flexible). If it is not possible then I will try to conveince with your approach.
Baskar: Thank you for the help. No issues..will try to explain my user.
Thank you all.
Regards,
Niket Talati