Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

sum measure flag

Hi everybody,

 

I have a table that is grouped by  "Distribuidores" and "Anunciantes". The meausures of the table are "Facturado Current  Year" and "Facturado Last Year". I want to know the percent of "Anunciantes" that were lost and earned over the year. So, for the "Anunciantes " lost I need to calculate the "Anunciantes" that were in LY but they are not in the current year. And for the anunciantes earned I need to calculate the "Anunciantes" that are in the current year but not on the last year.

The table is below. I made a flag that is 1 when the "Anunciante" are in LY but aren't in the current year. For the "Anunciantes" earned I mad the opposite.

 

Flag_1 = IF(NOT(ISBLANK([Facturado LY]));IF([Facturado LY]>0 && ISBLANK([Facturado]);1))

Flag_2= IF(NOT(ISBLANK([Facturado]));IF([Facturado]>0 && isblank([Facturado LY]);1))

 

My idea is sum the flags and divide by the total of rows. The problem is that I can't sum up a meausure or I don't know how to do it. Can you help please?

I exported the table. Var is Flag_1 and var3 is Flag_2 https://drive.google.com/file/d/1866-h9C99vdXrYqyeSrLhnlFGZsXSG4n/view?usp=sharing

 

 

Need Help

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous 

    will this code give you what you need?

    percentage new =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( data[Anunciante] );
        FILTER (
            data;
            OR ( ISBLANK ( data[Facturado LY] ); data[Facturado LY] <= 0 )
                && NOT (
                    ISBLANK ( ( data[Facturado] ) )
                        && data[Facturado] > 0
                )
        )
    );
    CALCULATE (
        DISTINCTCOUNT ( data[Anunciante] );
        FILTER ( data; NOT ( ISBLANK ( data[Facturado] ) ) && data[Facturado] > 0 )
    )
)


    I have reused some of the code you had put into the flags

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    it wasn't exactly what I wanted but it helped! Thank tou! sturlaws