Get the Maximum of A Measure or Sum of Calculated Column

[Anonymous]

Hi Power Bi Community,

I am quite new to Power BI an facing the following problem:
I want to create a Table visual with several fields containing the "Turnover" as a Measure and an Additional Measure which highlights if the Turnover of this sub group is the maximum turnover over all subgroups for that main Group:

ID	NAME	Main Group	Sub Group	SUMMED_TURNOVER [Measure]	Is_Max_Turnover [Here I need help]
123	AAA	Tech	Tech	1000	0
123	AAA	Tech	Keyboards	2000	1
123	AAA	Tech	Audio Device	1500	0
456	BBB	Finance	Finance	500	1
789	CCC	Cell Phone	Loading Cable	167	1

I already got so far as to get the Sum_of_turnover as a Measure:

SUMMED_TURNOVER = SUMX('FACT_TABLE', 'FACT_TABLE'[PRICE_PER_UNIT] * 'FACT_TABLE'[NO_OF_UNITS] * 'FACT_TABLE'[EXCHANGE_RATE] )

I also added it as a Calculated Column which I can use to show the summed value of it the Column SUMMED_TURNOVER instead of the measure:

TURNOVER_NOT_SUMMED = 'FACT_TABLE'[PRICE_PER_UNIT] * 'FACT_TABLE'[NO_OF_UNITS] * 'FACT_TABLE'[EXCHANGE_RATE]

Now I am stuck with identifying for each group the maximum SUMMED_TURNOVER.
I already tried some different approaches, the last one is:

Max in group =
CALCULATE (
   MAXX('FACT_TABLE',SUM('FACT_TABLE'[TURNOVER_NOT_SUMMED])),
   ALLEXCEPT('FACT_TABLE','DIMENSION_TABLE'[Sub Group])
)

Unfortunately this does not quite work as I hoped and adds a lot of values to my filtered data (I am using a subset to check my results as I am working with millions of records in my FACT_TABLE).

Does anyone have a hint for me on how to get the column/measure Is_Max_Turnover correctly set in DAX?
(In (Oracle)-SQL I would use MAX OVER PARTITION BY.)

Thank You and Kind Regards

Andre

[Anonymous]

Does anyone have an idea on how I can achieve this complex measure logic with PowerBI?

[v-cazheng-msft]

Hi @Anonymous 

You can try the following steps.

1 Create a Calculated column

max summmed turnover = CALCULATE(MAX(FACT_TABLE[Column_SUMMED_TURNOVER]),ALLEXCEPT(FACT_TABLE,FACT_TABLE[NAME]))

2 Create a Measure

Is_Max_Turnover = IF(SELECTEDVALUE(FACT_TABLE[max summmed turnover])=SELECTEDVALUE(FACT_TABLE[Column_SUMMED_TURNOVER]),1,0)

The result looks like this:

Here is the pbix.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Caiyun Zheng,
thank you for your suggestion. I will tyr to adapt it to my needs. Unfortunately I still do not see quite how to do it.
Firstly: The columns in my table visual are not all from the FACT_TABLE but related Dimension tables:
ID and NAME are from DIM_TABLE_A,
Main group is from DIM_TABLE_B,
AND SUB group is form DIM_TABLE_C.
As My relations are all 1 to many this should be of no concerns I think.

The first DAX formula you gave me, does not quite suit my needs I think.
CALCULATE(MAX(FACT_TABLE[Column_SUMMED_TURNOVER]),ALLEXCEPT(FACT_TABLE,FACT_TABLE[NAME]))
When I try to use it on my calculated column I get the max value for all categories not only those which exist for each category and furthermore only the max value not the max of the sum of the groups.

I also gave you not the complete data but only my visual. My data in the visual is also grouped by years and several other filters. That means my actual Data looks something like this:

Of course, my dimension tables contain many more rows with more different values. I just tried to give a minimal example.

The result I need is the same as in my first comment.

I tried already using MAX on the Calculated Column: Turnover but then I get the max value of it per other selected values and not the Max Summed Turnover per Subgroup for all Subgroups.

Can you give me a hint on how to proceed in my case with the more complex data?
Thank You and Kind Regards

Andre

[v-cazheng-msft]

Hi @Anonymous 

You can try the following steps.

1 Create two Calculated columns

sum_turnover_subgroup =

CALCULATE (

    [SUMMED_TURNOVER],

    ALLEXCEPT ( FACT_TABLE, DIM_TALBE_C[DIM_KEY_C] )

)

Max Turnover =

VAR max_summmed_turnover =

    SUMMARIZE (

        FACT_TABLE,

        "max_sum",

            CALCULATE (

                MAX ( FACT_TABLE[sum_turnover_subgroup] ),

                ALLEXCEPT ( FACT_TABLE, FACT_TABLE[DIM_DEY_B] )

            )

    )

RETURN

    IF (

        max_summmed_turnover = FACT_TABLE[sum_turnover_subgroup],

        1,

        0

    )

2 Create a Measure

Is_Max_Turnover = MAX(FACT_TABLE[Max Turnover])

The result looks like this:

For more details, you can refer the attached pbix file.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-cazheng-msft ,
thank you for your other answer. I checked your provided PBI file and tried to adapt it do my task. Unfortunately your step 1 does not work for me. I cannot create a Calculated-Column with the Caluculate Function. My Power BI marks CALCULATE as red with the advice that it is not a function.
Could be the reason that I get my data via Direct Query from Oracle tables?
Potentially also my filters are the reason that are applied on the data?

So my next try was then to execute your steps via Measures and then use the MAXX function where required. Unfortunately, as all columns are loaded before the measures are loaded I then only get the MAX again over all fields.

Do you maybe have another hint for me?
Does maybe the ALLSELECTED filter condition help me here?

Thank You and Kind Regards
Andre

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Try this Calculated column and Measure.

is_max_subgroup =

VAR rank_tur =

    RANKX (

        FILTER (

            ALL ( FACT_TABLE ),

            FACT_TABLE[DIM_DEY_B] = EARLIER ( FACT_TABLE[DIM_DEY_B] )

        ),

        CALCULATE (

            SUM ( FACT_TABLE[Turnover] ),

            ALLEXCEPT ( FACT_TABLE, FACT_TABLE[DIM_KEY_C] )

        ),

        ,

        DESC,

        DENSE

    )

RETURN

    IF ( rank_tur = 1, 1, 0 )

Is_Max = MAX(FACT_TABLE[is_max_subgroup])

The result looks like this:

For more details, you can refer the attached pbix file. If it doesn't work, could you please provide a copy of your pbix removing sensitive data?

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.