Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

Problems in row totals

Good afternoon!!
It is the first time I ask for support here, since I exhausted the ideas I had to find the solution and the time for delivery is just around the corner.
My problem is as follows:

I have created a matrix which is composed of 4 levels in its rows:

Level 1: Category
Level 2: Model Group

Level 3: Model Category
Level 4: Service Object


As the value of the array, I generated a measure that shows the percentage of utilization of each service object (Level 4), which shows the correct value for each unit. However, in the averages of the rest of the levels (Level 1 to level 3), the average it shows is not correct.

In this image (an excel with which I am doing the QA, the values are shown well:

The value of level 3, corresponds to the average of all the objects of level 4, while the value of level 2, corresponds to the average of the elements of level 3 and so on...


However, in the Power Bien array, while the value of the object (level 4) is displayed well, it is not delivering the value of the rest of the levels well:

The measure I am using to calculate the average is as follows:

UT12 = if(isblank([RU12]),blank(), divide(sumx(Cuadro_Utilizacion,[Rango_UT12]), sumx(Cuadro_Utilizacion,[Potencial_UT12]),0))


Please, if someone sees that I am doing wrong, I would greatly appreciate it!

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Syndicate_Admin , what is the role of checking isblank([RU12])

     

    Try simply like

    UT12 = divide(sumx(Cuadro_Utilizacion,[Rango_UT12]), sumx(Cuadro_Utilizacion,[Potencial_UT12]),0)

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    RU12 is the cost of acquiring the equipment when it was purchased 12 or more months ago. If the equipment was purchased before 12 months, I should not consider it in the UT12 measure.

    I made the change in the measure as indicated, and being only a change of substance and not of form, the results did not vary :c

    PBIX attachment with test data and excel file with expected results.
    https://drive.google.com/drive/folders/19xETAviKIWw0xAq4WvEU4ok0ni0_cV_-?usp=sharing