variable

[Milagros389]

I have this excel, with those columns. How do I do in power bi to generate a yes/no button, which if (for example) for area1 , I touch yes in amount1 and no in amount2, the total only adds amount1. If I touch yes to both, I add both amounts. I need the button to achieve all the combinations for each area and to be able to select whether in amount1 for area1 and no in amount1 for area2, for example. Thank you

[Anonymous · Accepted solution]

Hi @Milagros389 ,

I suggest you to create a calculated table with Area and Importe for slicer.

Slicer =
VAR _TABLE1 =
    ADDCOLUMNS (
        VALUES ( 'Table'[Area] ),
        "AreaOrder", REPLACE ( [Area], 1, 5, "" )
    )
VAR _TABLE2 =
    DATATABLE (
        "Importe", STRING,
        "ImporteOrder", INTEGER,
        {
            { "Importe 1", 1 },
            { "Importe 2", 2 }
        }
    )
RETURN
    GENERATE ( _TABLE1, _TABLE2 )

Measure:

Importe Total =
VAR _Importe1 =
    CALCULATE (
        SUM ( 'Table'[Importe 1] ),
        FILTER (
            'Table',
            'Table'[Area]
                IN CALCULATETABLE ( VALUES ( Slicer[Area] ), Slicer[ImporteOrder] = 1 )
        )
    )
VAR _Importe2 =
    CALCULATE (
        SUM ( 'Table'[Importe 2] ),
        FILTER (
            'Table',
            'Table'[Area]
                IN CALCULATETABLE ( VALUES ( Slicer[Area] ), Slicer[ImporteOrder] = 2 )
        )
    )
RETURN
    _Importe1 + _Importe2

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Milagros389]

Gracias pero no me sirve esa solucion. de alguna manera tengo que generar la variable para que pueda filtrar cada area y poder poner que si en importe uno y no en importe dos o cualquier combinacion, para cada area y que la media sume donde puse el si. Es muy complicado la verdad y hace semanas vengo buscando una solucion . 

Gracias, saludos

[Greg_Deckler]

@Milagros389 See if the Disconnected Table Trick works as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

Basically, your slicer for area1 and area2 would be your disconnected table and you could construct a measure that adds the correct amounts based on what is chosen.