DAX for Looping

[kkalyanrr]

Hello Team,

I'm trying to achieve the result(Green Colored Column) given below in the pic..
I've a static value to be added to the existing column values,

To implement this I ranked the values first and then using DAX I've found out the difference between Max value of the column and current cell value of the column..
Then my idea is the value 23 can be distributed as 9 for B,9 for C and 5 for D...
Please help.

[Anonymous · Accepted solution]

Hi @kkalyanrr ,

So the logic should be if the [static value minus the cumulative difference value] is greater than the [difference value] and then return the [difference value], if the [static minus the cumulative difference value] is less than the [difference value] and greater than 0, then return [static value minus the cumulative difference value] itself, and if the [static value minus the cumulative difference value] is less than 0 then return 0?

You may refer this formula.

Column = 
var static_value = 23
var cumulative_value = CALCULATE(SUM('Table'[difference]),FILTER('Table','Table'[Group]<EARLIER('Table'[Group])))
var minus_value = static_value-cumulative_value
return
IF(minus_value>='Table'[difference],'Table'[difference],IF(minus_value<'Table'[difference]&&minus_value>0,minus_value,0))

Best Regards,

Jay

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

Desired column CC = 
VAR fixvalue = 23
VAR rankgroup =
    RANKX ( Data, Data[Group],, ASC )
VAR rankcondition =
    RANKX ( Data, Data[ColumnA] * 100 + RANKX ( Data, Data[Group],, ASC ),, ASC )
VAR maxcolumnA =
    MAX ( Data[ColumnA] )
VAR difffrommax = maxcolumnA - Data[ColumnA]
VAR newtable =
    ADDCOLUMNS (
        Data,
        "@rankcondition",
            RANKX ( Data, Data[ColumnA] * 100 + RANKX ( Data, Data[Group],, ASC ),, ASC ),
        "@difffrommax", maxcolumnA - Data[ColumnA]
    )
VAR filternewtable1 =
    FILTER ( newtable, [@rankcondition] <= rankcondition )
VAR filternewtable2 =
    FILTER ( newtable, [@rankcondition] < rankcondition )
RETURN
    SWITCH (
        TRUE (),
        SUMX ( filternewtable1, [@difffrommax] ) <= fixvalue, difffrommax,
        SUMX ( filternewtable2, [@difffrommax] ) <= fixvalue, fixvalue - SUMX ( filternewtable2, [@difffrommax] ),
        0
    )