MINX over SUMX Calculation Item shows Totals (.pbix included)

[Titatovenaar2]

Hi everyone,

.PBIX File 

I am trying to get MIN and MAX values on the Total Row instead of a SUM as shown below.

• This works well for Calculation Item 'Metric'[Units], all correct values in Green.
• For Calculation Item 'Metric'[Revenues/Unit] something goes wrong (values in Red). It shows the correct summed value for measure [Matrix Value] in Green, but for both [Matrix Max] and [Matrix Min] they show the same summed up value.
• In Blue the expected values to be shown in the Totals Row.

The measures I use:

Matrix Value = 
CALCULATE(
    SUMX(Trends, [Value])
)

Matrix Min = 
MINX(VALUES(Trends[Source]), [Matrix Value])

Matrix Max = 
MAXX(VALUES(Trends[Source]), [Matrix Value])

And the Calculation Group 'Metric' has the following Calculation Items:

Units = 
IF(
    NOT ISBLANK(SELECTEDVALUE('Def: End-Market'[End-Market]))
    ,CALCULATE(
        SELECTEDMEASURE()
        ,'Trends'[Category] == "Units"
    )
)

Revenue = 
CALCULATE(SELECTEDMEASURE(), 'Trends'[Category] == "Semi")

Revenue/Units =
SUMX(
    SUMMARIZE(Trends
        ,'Def: End-Market'[End-Market]
        ,'Trends'[Source]
        ,'Def: Year'[Year]
        ,"Rev/Units"
            ,DIVIDE(
                CALCULATE(SELECTEDMEASURE(),'CG: Metric'[Metric] = "Revenues")
                ,CALCULATE(SELECTEDMEASURE(),'CG: Metric'[Metric] = "Units")
            )
    ),
    [Rev/Units]
)

The Revenue/Units returns values on the levels of [End-Market],[Year],[Source]. How can I return the MIN and MAX on those levels too in a separate measure, that work for both Calculation Items?

Kind regards,

Igor

[Titatovenaar2]

Thanks for thinking with me! It's a solid workaround to be able to deal with this issue 🙂

[v-yueyunzh-msft]

Hi , @Titatovenaar2 

Thanks for your .pbix file you provide, i think you put the wrong field on column . Now , you put the 'CG: Metric[Metric]' field on the column , but this felid has no relationship between other table. It will not be filtered by it. And i filter the data , i think now the data is also wrong in it , you can see when we filter in this condition:

The total should be 92.756  not the 181:

We need to put the 'Trends[Category]' on the visual then we can get the right total and put the measures you have created , it also works good in it :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Titatovenaar2]

Thanks for the fast reply and the compliment!

I understand that you would think using Trend[Category] would solve this issue, but I actually don't use the 'Revenues/Unit' Category data for this visual currently. It is a little bit misleading, but I will show what I mean:

the 'CG: Metric'[Metric] is the calculation group with calculation items 'Units', 'Revenues' and 'Revenues/Units'.

The 'Revenues/Units' calculation item takes data from the  Trend table, but only from categories 'Semi' and 'Units'. More specifically: it divides the total of Trend[Value] where Trend[Category] = 'Semi' with the total of Trend[Value] where Trend[Category] = 'Units' .

So because of that I created a calculation group instead. because if I use Trend[Category] I won't be able to see the divide, because it separates the Semi's from the Unit's in separate columns.

Kind regards,

Igor

[v-yueyunzh-msft · Accepted solution]

Hi ,  @Titatovenaar2 

Sorry , i may get a missing in your need before. I test it in my side , i think it may caused by the filter context in the dax. when you use the calculate group , the max value calculation will be :

The most outside function is the sumx , so we always get the sum of the value not the max.

I am lack for the using of the calculation group , but it can be solved just in the desktop , you can also refer to :

(1)We need to re-create the three measures in the desktop instead of the calculation group.

[1]:

Units 2 =

IF(

    NOT ISBLANK(SELECTEDVALUE('Def: End-Market'[End-Market]))

    ,CALCULATE(

        [Matrix Value]

        ,'Trends'[Category] == "Units"

    )

)

[2]:

Revenue 2 =

CALCULATE([Matrix Value], 'Trends'[Category] == "Semi")

[3]:

Revenue/Units 2 =

SUMX(

    SUMMARIZE(Trends

        ,'Def: End-Market'[End-Market]

        ,'Trends'[Source]

        ,'Def: Year'[Year]

        ,"Rev/Units"

            ,DIVIDE(

                CALCULATE([Matrix Value],'CG: Metric'[Metric] = "Revenues")

                ,CALCULATE([Matrix Value],'CG: Metric'[Metric] = "Units")

            )

    ),

    [Rev/Units]

)

(2)Then we need to click "New Table" to create a table like this:

 ColumnName = {"Units","Revenue","Revenue/Units"} 

(3)We need to create three another measures:

[1]:
 

The Raw Value = var _column_name = MAX(ColumnName[Value])

return

SWITCH(_column_name ,

"Units",[Units 2],

"Revenue",[Revenue 2],

"Revenue/Units",[Revenue/Units 2])

[2]:

Max Value in Matrix =

var _column_name = MAX('ColumnName'[Value])

var _units= MAXX( VALUES('Trends'[Source]) , [Units 2])

var _Revenue=MAXX( VALUES('Trends'[Source]) , [Revenue 2])

var _Revenue_Units=MAXX( VALUES('Trends'[Source]) , [Revenue/Units 2])

return

SWITCH(_column_name ,

"Units",_units,

"Revenue",_Revenue,

"Revenue/Units",_Revenue_Units)

[3]:

Min Value in Matrix =

var _column_name = MAX('ColumnName'[Value])

var _units= MINX( VALUES('Trends'[Source]) , [Units 2])

var _Revenue=MINX( VALUES('Trends'[Source]) , [Revenue 2])

var _Revenue_Units=MINX( VALUES('Trends'[Source]) , [Revenue/Units 2])

return

SWITCH(_column_name ,

"Units",_units,

"Revenue",_Revenue,

"Revenue/Units",_Revenue_Units)

(4)Then we can put this measures on the visual and we can get the result in the end:

 

I hope it will be helpful if you want to realize it in calculation group which i have less knowledge it.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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