Forum Discussion
Helper IAdding 1 to specific Column and Row based on the cell Value
I have a dilemma that for the longest time I've been struggling with.
It was fun tackling the problem but I need some help from the community.
I would like to add 1 to a specific column and row. In the clip below that would be
column 0 and row 0 circled in Red. Only adding 1 to the minimum values of both the row and column.
Which would be 0+1 to each cell of the row and column in the example below.
Attached is an Excel file with the Data Model loaded with a sample of the data:
https://drive.google.com/file/d/1oZq2bLT6-kteL4WqR5Xgp4XVdgSqnA-w/view?usp=sharing
There are other Measures that measure the width of the row, and height of the column.
Just missing this piece for a visual representation.
The catch is the minimum value is not alway 0. Minimum value depends on how I pivot the data.
I think I'm close but I cannot figure out how to find the miniumn value of the entire table.
In the end I would apply condtional formatting to highlight both the row and column.
Thanks You.
Finally. This is not elegant but it works.
The variable minsum1 finds the minimum value of the table.
The 2nd part of the measure substracts one from the minimum sum value (as opposed to adding 1 which originally asked for) from row 0 and column 0.
=VAR minsum1
= CALCULATE(sumx(raw_data, raw_data[Value]),
FILTER(ALL(raw_data[Vref]), raw_data[Vref] = 1),
raw_data[CDC (Coarse+Fine)] = 0, All(raw_data[Vref]))
return
if(or(sum(raw_data[Vref])=0 && sum(raw_data[Value]) = minsum1,
(sum(raw_data[CDC (Coarse+Fine)])=0 && sum(raw_data[Value]) =minsum1)),
sum(raw_data[Value]) -1, Sum(raw_data[Value]))
I then used conditionally formatting to highlight the negative numbers in the table.
The one problem someone can please help me with is converting the value created from the variable minsum1
from a dynamic value to a constant value. That's likely a different post.
Thank you everyone.
4 Replies
ronbermudezAnyone? Can some throw me a hint? 😀
The struggle is real.
I can hardcode the minimum value, but the minimum values is always changing based on how I want to view the data.
Let me see if I can get this....
Thanks...
Hi ronbermudez
I'm so sorry that I can't really understand what you mean. What is the result you want in the end ? Could you provide the corresponding screenshot or the result of the comparison ? This will help me solve the problem easier .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- ronbermudez
Anonymous
Current:
As you can see in row 0 and column 0, all the cell values are zero. The cell contents are a sum.
This is expected. Adding 1 to the lowest minumum sum value for column 0 and row 0.
The lowest minumum sum value changes. It is not always zero, but it is always the minimum value of the entire table.
Hope this helps further.
I'm still stuck.
Thank you.
- ronbermudez
Finally. This is not elegant but it works.
The variable minsum1 finds the minimum value of the table.
The 2nd part of the measure substracts one from the minimum sum value (as opposed to adding 1 which originally asked for) from row 0 and column 0.
=VAR minsum1
= CALCULATE(sumx(raw_data, raw_data[Value]),
FILTER(ALL(raw_data[Vref]), raw_data[Vref] = 1),
raw_data[CDC (Coarse+Fine)] = 0, All(raw_data[Vref]))
return
if(or(sum(raw_data[Vref])=0 && sum(raw_data[Value]) = minsum1,
(sum(raw_data[CDC (Coarse+Fine)])=0 && sum(raw_data[Value]) =minsum1)),
sum(raw_data[Value]) -1, Sum(raw_data[Value]))
I then used conditionally formatting to highlight the negative numbers in the table.
The one problem someone can please help me with is converting the value created from the variable minsum1
from a dynamic value to a constant value. That's likely a different post.
Thank you everyone.
