Power BI Power Pivot Use a different measure for grand total than what was used for the column

[zigmondsrh]

I have a column that is showing the average head count required for each of the 48 thirty minute intervals in a 24 hour day.  I need the grand total row to divide the grand total result by 2.  I do not want the column meausre to divide by 2.  I only need the grand total to be divided by 2.  The following is the measure currently in the pivot table.

=SUMX(SUMMARIZE(subquery_Forecast_Accuracy_INT,[INTRVL]),CALCULATE([PWBI.REQHOURS_AVG_ACTUAL]))

"subquery_Forecast_Accuracy_INT" is the name of the table.

"[INTRVL]" is the summarize level or Group By statement.

"[PWBI.REQHOURS_AVG_ACTUAL]" is simply taking the column with the required hours data and dividing that by a distinct count of all the records with actual data.

Since there are two intervals in an hour, I have to divide the grand total by 2 to get the total hours required for the day.  How can I get the grand total to divide by 2 without applying the divide by 2 to the individual interval data?

[Anonymous]

Hi @zigmondsrh 

Maybe you can try to use the HASONEVALUE() function.

HASONEVALUE function (DAX) - DAX | Microsoft Learn

For example, I create a set of sample:

Then the HASONEVALUE() can return the True or False in different level of the matrix:

Measure = 
HASONEVALUE('Table'[Value])

So you can use IF() function to get the right target like this:

MEASURE =
VAR _A =
    SUMX (
        SUMMARIZE ( subquery_Forecast_Accuracy_INT, [INTRVL] ),
        CALCULATE ( [PWBI.REQHOURS_AVG_ACTUAL] )
    )
RETURN
    IF ( HASONEVALUE ( YourValue ), _A, _A / 2 )

Best Regards

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...