Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
7 years ago
Solved

Measure does not total and disappears values

Hi!

 

I have a very disgusting issue here: 

 

First, the measure simply doesn't do the correct sum. 

 

The strange thing is that measure already uses SUMX. I tried several things without success. Then, the problem got worse. The highlighted measure when is under a low-level filter shows the data. But when the measure is without any filters the data simply disappear.  

The goal of that measure is to show the number of filtered movements. The first measure is the total of movements. The wrong one is the filtered movements. 

 

Well, that's all. I know the importance of the sample of data so my little file can be found here: 
https://1drv.ms/u/s!AtTnrgPUQzQCgrN2hHAzvVKnJqBt9Q

Hope you guys can help me. 

 

Thanks.

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi AlejandroPCar,

     

    If 73 is the right answer, please try to wrap the MIN part with "calculate". 

    Actividades Adultos Inmediato 2 =
VAR adult =
    SUMX (
        VALUES ( Adult_Quindio[id_persona] ),
        CALCULATE (
            SUM ( Jerarquia_Adult[cantidad] ),
            FILTER (
                Jerarquia_Adult,
                AND (
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) >= Jerarquia_Adult[edadinicial],
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) < Jerarquia_Adult[edadfinal]
                )
            )
        )
    )
RETURN
    adult

    Measure-does-not-total-and-disappears-values2

     

    BTW, maybe you can establish a *:* relationship to avoid a new table. But this is a suggestion based on the heavily truncated data.

     

     

    Best Regards,

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi AlejandroPCar,

     

    This could be a context issue. I'm afraid you can't get your desired results. What would you like to get with them?

    Let's have a look at the first issue.

    Actividades Adultos Inmediato = 
    VAR adult = 
            SUMX(
                VALUES(Adult_Quindio[id_persona]),
                CALCULATE(
                    SUM(Jerarquia_Adult[cantidad]),
                    FILTER(
                        Jerarquia_Adult,
                        AND(
			// What do you think the MIN(Adult_Quindio[annodec]) should be?
			// It's two different values in the rows while it's a single value in the Total.
                            MIN(Adult_Quindio[annodec]) >= Jerarquia_Adult[edadinicial],
                            MIN(Adult_Quindio[annodec]) < Jerarquia_Adult[edadfinal]
                        )
                    )
                )
            )
RETURN
    adult

    Measure-does-not-total-and-disappears-values

    Actually, the result 72 is 36 * 2. Why? The context of MIN(Adult_Quindio[annodec]) is from VALUES(Adult_Quindio[id_persona]) that is a row context. MIN ignores the row context. So it's 58.25 in the Total and there are two pieces. 

    You can adjust your formula according to the explanation above. Maybe you can post the idea that you are going to achieve.

     

    BTW, please don't share any confidential data here.

     

     

    Best Regards,

    • AlejandroPCar's avatar
      AlejandroPCar
      Icon for Helper IV rankHelper IV
      7 years ago

      Hi! v-jiascu-msft

       

      Wow, your explanation was incredibly useful. So... all the time the problem was the MIN... I am looking for a filter like "the value of the row 1 in the column A in the Table A is greater than the value of the row 1 in the column A in the Table B ". But, my question is now, to avoid MIN... How can I filter columns from two different tables in one FILTER? 

       

      Something like...

       

      CALCULATE(
	[Measure],
	FILTER(
		(Tables),
		TableA[Value] > TableB[Value]
      		)
      	)

       

       

      I believed that MIN just give the value of that selected row. Well thats true when a single row is filtered, but... what a mistake. 

      Thanks again...

       

      And, I know it... all data I publish here is already heavy truncated.

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi AlejandroPCar,

         

        If 73 is the right answer, please try to wrap the MIN part with "calculate". 

        Actividades Adultos Inmediato 2 =
VAR adult =
    SUMX (
        VALUES ( Adult_Quindio[id_persona] ),
        CALCULATE (
            SUM ( Jerarquia_Adult[cantidad] ),
            FILTER (
                Jerarquia_Adult,
                AND (
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) >= Jerarquia_Adult[edadinicial],
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) < Jerarquia_Adult[edadfinal]
                )
            )
        )
    )
RETURN
    adult

        Measure-does-not-total-and-disappears-values2

         

        BTW, maybe you can establish a *:* relationship to avoid a new table. But this is a suggestion based on the heavily truncated data.

         

         

        Best Regards,