Forum Discussion

ErnestoSanchez's avatar
ErnestoSanchez
Frequent Visitor
6 years ago

Average ov Averages

Hi everybody,

Thanks for take your time to help me.

 

I have a two clustered column charts

In the first one I show absolut values (80,  54, 198, etc.)

In the second one I show relatives values related with the firts chart (83.84%, 67.50%)

 

All behavior is controled by some slicers, where users can select depend of their needed.

 

I need one more element.

A card where I want to show the AVERAGE of the AVERAGES shown in the second chart.

Look at the image.

 

AVERAGE OF AVERAGES .PBIX file 

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    ErnestoSanchez try this measure

     

    Measure = AVERAGEX( VALUES ( Consolidado[PAIS] ),  [GraduadosSIPrograma%] )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • ErnestoSanchez's avatar
      ErnestoSanchez
      Frequent Visitor
      6 years ago

      Thank parry2k .

      As you can see, I am using a Hierarchy (sexo, pais, financiador, socio, sede, número de promoción) and a Drill. 

      Your solution works fine when I have the Drill on [PAIS],

      But, when I change the selection... It stop working fine. It show me a bad result.

       

      Any idea...??

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ErnestoSanchez,

    You can create a measure with variable table to fix the calculated result of the first aggregated calculate, then you can use iterator function on this table to apply the second aggregated mode.

    Sample:

    measure =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [Category],
        "AVG", AVERAGE ( Table[Amount] )
    )
RETURN
    AVERAGEX ( summary, [AVG] )

    Regards,

    Xiaoxin Sheng

    • ErnestoSanchez's avatar
      ErnestoSanchez
      Frequent Visitor
      6 years ago

      Thanks Anonymous ,

      I have been trying to figure out what do you mean with your soluction,

      But I couldn't concretize it in my .pbix file to get the correct results.

       

      May I ask you to give me more details.

       

      Thanks..

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi ErnestoSanchez,

        You can try to use the following measure formulas if it meets your requirement:

        Graduados% based on selection = 
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Consolidado ),
        [SEXO],
        [PAIS],
        [FINANCIADOR],
        [SOCIO],
        [SEDE],
        [NÚMERO DE PROMOCIÓN],
        "Percent", DIVIDE (
            CALCULATE (
                SUM ( Consolidado[INSCRITOS] ),
                Consolidado[INDICADORES] = "Jóvenes inscritos que se graduan"
            ),
            CALCULATE (
                SUM ( Consolidado[soTOTAL] ),
                Consolidado[INDICADORES] = "Jóvenes inscritos que se graduan"
            ),
            BLANK ()
        )
    )
RETURN
    AVERAGEX ( summary, [Percent] )

        Regards,

        Xiaoxin Sheng