Calculating a Sum of Max Values in an array (of Slicers-Values)

[bagabo]

Hi! 

I built myself an Index of System-installs, so i can calculate the max(index) of some Period (e.g. 2021, December) and have the correct Number of installed Systems at the end of this month.

This works just fine for one system checked on a slicer. However, if i want to know how much of a System-family (like 2 similar systems), I'd need to calculate a sum of these two maxed values. 

Sum of System Index Measure = 
    VAR _SystemArray = VALUES([System])   //Array of systems

RETURN

?

Usual: MAX([System] <- SUM for all chosen systems

As in the image, I would create an array with all the systems the user chose in the slicer. But how do I sum up all of the max(Index) of this array of systems?

Thankful for any information!

Regards

[v-eqin-msft · Accepted solution]

Hi @bagabo ,

Based on my max measure , I used system and date as grooups. So when you use hierarchy and drill down, it will not return correct values. 

So to my knowledge, you could use HASONEVALUE() to catch the correct hierarchy, as shown below:

test hasonefilter =
IF (
    HASONEFILTER ( Tabelle[Date].[Tag] ),
    1,
    IF (
        HASONEFILTER ( Tabelle[Date].[Monat] ),
        2,
        IF (
            HASONEFILTER ( Tabelle[Date].[Quartal] ),
            3,
            IF ( HASONEFILTER ( Tabelle[Date].[Jahr] ), 4 )
        )
    )
)

So please try:

by year = CALCULATE(MAX('Tabelle'[Index]),FILTER('Tabelle',[System]=MAX('Tabelle'[System]) && YEAR([Date])=YEAR(MAX('Tabelle'[Date])))) 

by quarter = CALCULATE(MAX('Tabelle'[Index]),FILTER('Tabelle',[System]=MAX('Tabelle'[System]) && QUARTER([Date])=QUARTER(MAX('Tabelle'[Date]))))

by month = CALCULATE(MAX('Tabelle'[Index]),FILTER('Tabelle',[System]=MAX('Tabelle'[System]) && MONTH([Date])=MONTH(MAX('Tabelle'[Date]))))

Measure 2 = 
var _year=SUMMARIZE(ALLSELECTED(Tabelle),[Date].[Jahr],[System],"max",[by year])
var _yearsum=SUMX(_year,[max])

var _quarter=SUMMARIZE(ALLSELECTED(Tabelle),[Date].[Quartal],[System],"max",[by quarter])
var _quartersum=SUMX(_quarter,[max])

var _month=SUMMARIZE(ALLSELECTED(Tabelle),[Date].[MonthNo],[System],"max",[by month])
var _monthsum=SUMX(_month,[max])


return  
IF(HASONEFILTER(Tabelle[Date].[Tag]),[sum of max],   IF(HASONEFILTER(Tabelle[Date].[Monat]),_monthsum,  IF(HASONEFILTER(Tabelle[Date].[Quartal]),_quartersum, IF(HASONEFILTER(Tabelle[Date].[Jahr]),_yearsum))))

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

[v-eqin-msft]

Hi @bagabo ,

Please try:

max = CALCULATE(MAX('Tabelle'[Index]),ALLEXCEPT(Tabelle,Tabelle[System],Tabelle[Date]))

sum of max = 
VAR _TABLE= FILTER(DISTINCT(Tabelle),[Index]=[max])
RETURN SUMX(_TABLE,[Index])

Output:

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

[bagabo]

Any further information? Your input was quiet helpful so far!

[bagabo]

Hi @v-eqin-msft 

This works pretty good if selected a single Date value. After a Drillup to like Month, Quarter or Year the result gets wrong. 

As you can see in the image, if "year" selected, the reuslt is 9. I'd expect it to be still 6, as it's still the maximum number of installed systems in this year.

Regards

[bagabo]

Hi! @v-eqin-msft @amitchandak 

I built a little demo dataset: Sum_of_max_values.pbix 

As you can see in the image, i got 3 system types with an index and date. The index stands for the cumulative number of installed systems.
For example: On 02.01.2020 i got a total of 3 Systems of type A. (1 System before, 2 installs on 02.01.2020)

This works by using the max-function in the visuals. The problems occurs if im selecting 2 or more systems.

Like in the picture, system A and system C are selected.
For 02.01.2020: MAX System A: 3
                         MAX System C: 3

So in total i want to count 6 systems. However, because of the max function it shows only 3. 
So i'd need to somehow calculate a sum of these individual max-values of a selected slicer-array.

I hope it is now a little more understandable

Gabriel

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-eqin-msft]

Hi @bagabo ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more detail information to help us clarify your scenario?

Refer to:

How to Get Your Question Answered Quickly

How to provide sample data in the Power BI Forum

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

[amitchandak]

@bagabo ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.