Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

Sum measures in matrix wrong total (several summarizes)

Hello

I have carefully read all suggestions regarding how to solve wrong totals in matrix columns, however the solutions seems do not work for me.

I have a matrix like this.:

Several measures in columns and group of materials (agrupacion) and month in rows.

the measure Max Sales seems to calculate correct in rows, but do not calculate correct in totals.

Max. Sales = if((measurement[Actual Available])<0,([T.Sales]+[BO Pen Cast]+measurement[accDisponible Real]),[T.Sales]+[BO Pen Cast])
I have calculate also "Max. Sales bis" to summarize by Group of material.
So total is correct in the column, and in the Total per material at Material level row, but do not shows the detail per month.
Max Sales up to = sumx(SUMMARIZE(Mastermaterialsap,Mastermaterialsap[Grouping],Mastermaterialsap[Profit Center]),[Max. Sales])

What I need is to show as "Max. Sales" but with correct total. I don't know how to add two "Summarizes" by material and by month if the categories are in different tables (master material and calendar).

Same problem arize to other columns in same matrix.

I will appreciate your help.

thanks

sonia

1 Reply

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Syndicate_Admin ,

     

    Since there is no specific pbix file for you, you can refer to my similar template data below.

    My measure:

    z1 = MAX('Table'[A])*MAX('Table'[B])

    Output:

    Here, the total is wrong. It is because when a Measure is used on rows in a table, the column total for those rows is not calculated based on a sum of the results in the rows, but instead it calculates using the same Measure formula and applies it to the total of the data selected.

    So create another measure to correct it.

    correct_z1 =
IF (
    COUNTROWS ( VALUES ( 'Table'[group] ) ) = 1,
    [z1],
    SUMX ( VALUES ( 'Table'[group] ), 'Table'[z1] )
)

    Final output:

    You can try to use SUMX function to create a measure like above. Or you can use the HASONEFILTER to create a measure like below.

    correct_z1 =
IF (
    HASONEFILTER ( 'Table'[group] ),
    [z1],
    SUMX ( VALUES ( 'Table'[group] ), [z1] )
)

    You can reference the following document.

    How to Make Measures Total Correctly in Power BI Tables - ArcherPoint

     

    Best Regards,
    Community Support Team _ xiaosun

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