How to get the total of a measure and show it in all rows of a table

[alespooletto]

I have an easy measure that does the following 

// All Other Employees Measure
Sum(
    Aggr(
        {< Employee= >}  // Ignore selection of Employee
        Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,
        MainSFC, Employee
    )
)
/
(
Sum(
    Aggr(
        {< Employee= >}  // Ignore selection of Employee
        Count(distinct WorkingDay) * 7.5 * 60,
        Employee
    )
)
    -
	Sum(Absence) * 60

)

I then add another measure, which is the same but with only TOTAL suffix added to the two SUM functions.

This results in a table with the following values

But as you can see, while the values on the left are correct, the results on the right are not what I was looking for. The right one should be all 75.4% for each single row, because it's the total value at the top of the first column to the left. 

Let's make an example: 

Measure 1	Total measure
average: 50	average: 50
25	50
75	50

So the final result should be 50, because the 2 rows gave me a total average of 50, and all rows in the second measure should thus have 50. 

[PrashantSangle]

Since we don't have idea about your data, it is difficult to say which one is correct & which one is wrong. according to you which one is correct? what are you trying to achieve? Can you explain with sample data?

Also, where do you need our help? in understanding the expression logic or need help in correct expression?

Regards,

Prashant Sangle

[alespooletto]

Hello @PrashantSangle , thank you for the answer.

I tried to add more context to the question, let me know if you need more details, but the values to the left are simply the results of the measure, and I want to make a measure that will give me only the total avg (the top bold 75,4%) value at the top for all of the rows.

[brunobertels]

Hi 

May be try to add NO DISTINCT qualifier or TOTAL Qualifier 

// All Other Employees Measure

Sum( NO DISTINCT

    Aggr(

        {< Employee= >}  // Ignore selection of Employee

        Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,

        MainSFC, Employee

    )

)

/

(

Sum(

    Aggr(

        {< Employee= >}  // Ignore selection of Employee

        Count(distinct WorkingDay) * 7.5 * 60,

        Employee

    )

)

    -

Sum(Absence) * 60

)

[PrashantSangle]

@alespooletto , is it possible for you to share the app? 

Actually it is simple by using TOTAL keyword in set expression, but as I can see your orginal expression is complex in itself only, so difficult to give you correct answer.

But in simple scenario, we use

Sum(TOTAL<dimension_name> sales)

If you used dimension name along with total then it will give you total sales at group of dimension level.

Regards,

Prashant Sangle

[alespooletto]

@PrashantSangle unfortunately it's not possible, because it contains private data. I will try to make a sample of data tomorrow, and give it back to you. 

I tried with that simple approach, the Sum (total <fld> ) but it gives me a wrong number at the end of the total. 

Tomorrow I will try to provide it.

[alespooletto]

Hello @PrashantSangle 

I tried to collect only a few of the data, you should be able to use the formula above to retrieve the exact situation. 

[PrashantSangle]

I can see only 3 columns in attached excel. MainSFC, Dipendente and Tempo_Medio

In your expression there are few more fields. like Employee, ACTION_CODE, Avg_time, WorkingDay, Absence

Regards,

Prashant Sangle

[alespooletto]

Hi @PrashantSangle I must have attached the wrong file, you can find a table containing all the information here

[PrashantSangle · Accepted solution]

Hi @alespooletto ,

do you want below output

if yes, then use below expression

Sum(Total

    Aggr(

        {< Employee= >}  // Ignore selection of Employee

        Count({< ACTION_CODE = {'LABOR_OFF'} >} distinct MainSFC) * Avg_time,

        MainSFC, Employee

    )

)

/

(

Sum(Total

    Aggr(

        {< Employee= >}  // Ignore selection of Employee

        Count(distinct WorkingDay) * 7.5 * 60,

        Employee

    )

)

    -

Sum(Total Absence) * 60

)

Regards,

Prashant Sangle