cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How i can make "what if scenario" applied only in one (table) row

How can I make a calculation (New value) on a single line (only in one row) of the table, by selecting from a list containing numbers (1-4).
For example, I select the number 1 from the list and I would like to apply the statement in line 1, column New Value, of the table.

 

See in the picture below how I would like to work.

 

Capture.PNG

2 ACCEPTED SOLUTIONS


@kkapsokavadis wrote:

Finally, i found the solution.

i've made 2 measures, one for each Id (Row Id & Selection Id).

 

Sum_Choises_PK = SUM( Choises[Choises_PK] )

Sum_wif_id = SUM(wif[id])

 

Then i've made the Measure for "what if Value"

 

wif Value = if (wif[Sum_wif_id]=Choises[Sum_Choises_PK]; sum(wif[Value]) + sum(wif[Value]) * Parameter[Parameter Value] / 100 ; sum(wif[Value]))

 

Capture.PNG

 

Now, im faced with with a new (2) problems:

 

As you can see:

 

a. The Total of the [wif Value] is incorrect. The correct value is 120.000

b. The Calculated Column [New Running total] --> (New Quick Measure -> Running Total), also is incorrect 

 

New Running total =
CALCULATE(
    'wif'[wif Value];
    FILTER(
        ALLSELECTED('wif'[id]);
        ISONORAFTER('wif'[id]; MAX('wif'[id]); DESC)
    )
)

 

any suggestions are welcome!!


@kkapsokavadis

You can try to change the measure a little bit.

wif Value = 
VAR summurizedTbl =
    SUMMARIZE (
        wif,
        wif[id],
        "total value", IF (
            wif[id] = Choises[Sum_Choises_PK],
            SUM ( wif[Value] )
                + SUM ( wif[Value] ) * Parameter[Parameter Value]
                    / 100,
            SUM ( wif[Value] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( wif[id] ),
        IF (
            wif[Sum_wif_id] = Choises[Sum_Choises_PK],
            SUM ( wif[Value] )
                + SUM ( wif[Value] ) * Parameter[Parameter Value]
                    / 100,
            SUM ( wif[Value] )
        ),
        SUMX ( summurizedTbl, [total value] )
    )

Capture.PNG

 

See more details in the attched pbix file.

View solution in original post

Works Perfect!

 

Thanx!!!!

View solution in original post

3 REPLIES 3
Frequent Visitor

Finally, i found the solution.

i've made 2 measures, one for each Id (Row Id & Selection Id).

 

Sum_Choises_PK = SUM( Choises[Choises_PK] )

Sum_wif_id = SUM(wif[id])

 

Then i've made the Measure for "what if Value"

 

wif Value = if (wif[Sum_wif_id]=Choises[Sum_Choises_PK]; sum(wif[Value]) + sum(wif[Value]) * Parameter[Parameter Value] / 100 ; sum(wif[Value]))

 

Capture.PNG

 

Now, im faced with with a new (2) problems:

 

As you can see:

 

a. The Total of the [wif Value] is incorrect. The correct value is 120.000

b. The Calculated Column [New Running total] --> (New Quick Measure -> Running Total), also is incorrect 

 

New Running total =
CALCULATE(
    'wif'[wif Value];
    FILTER(
        ALLSELECTED('wif'[id]);
        ISONORAFTER('wif'[id]; MAX('wif'[id]); DESC)
    )
)

 

any suggestions are welcome!!


@kkapsokavadis wrote:

Finally, i found the solution.

i've made 2 measures, one for each Id (Row Id & Selection Id).

 

Sum_Choises_PK = SUM( Choises[Choises_PK] )

Sum_wif_id = SUM(wif[id])

 

Then i've made the Measure for "what if Value"

 

wif Value = if (wif[Sum_wif_id]=Choises[Sum_Choises_PK]; sum(wif[Value]) + sum(wif[Value]) * Parameter[Parameter Value] / 100 ; sum(wif[Value]))

 

Capture.PNG

 

Now, im faced with with a new (2) problems:

 

As you can see:

 

a. The Total of the [wif Value] is incorrect. The correct value is 120.000

b. The Calculated Column [New Running total] --> (New Quick Measure -> Running Total), also is incorrect 

 

New Running total =
CALCULATE(
    'wif'[wif Value];
    FILTER(
        ALLSELECTED('wif'[id]);
        ISONORAFTER('wif'[id]; MAX('wif'[id]); DESC)
    )
)

 

any suggestions are welcome!!


@kkapsokavadis

You can try to change the measure a little bit.

wif Value = 
VAR summurizedTbl =
    SUMMARIZE (
        wif,
        wif[id],
        "total value", IF (
            wif[id] = Choises[Sum_Choises_PK],
            SUM ( wif[Value] )
                + SUM ( wif[Value] ) * Parameter[Parameter Value]
                    / 100,
            SUM ( wif[Value] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( wif[id] ),
        IF (
            wif[Sum_wif_id] = Choises[Sum_Choises_PK],
            SUM ( wif[Value] )
                + SUM ( wif[Value] ) * Parameter[Parameter Value]
                    / 100,
            SUM ( wif[Value] )
        ),
        SUMX ( summurizedTbl, [total value] )
    )

Capture.PNG

 

See more details in the attched pbix file.

View solution in original post

Works Perfect!

 

Thanx!!!!

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors