Wrong values on total

DAX DAX Calculations
1

Hi, I have a problem with a simple metric. I would like the following metric to show me the sum that I am calculating in the given lines.
Code:
Share _% _ 1 = IF (CALCULATE (SUM (‘Date’ [Hours]))> 120,
DIVIDE (DISTINCTCOUNT (‘Date’ [Doc]), [count], 0), BLANK ())

Link to pbx: https://drive.google.com/file/d/1GwvPIfSFlQSwFM7PIgnZC5Mo6RRjO1vH/view?usp=sharing

2
21260×731 50.5 KB

2

Hello @Harris,

Thank You for posting your query onto the Forum.

Well to fix the totals you’ll just be required to write a small additional formula. One of our expert @Greg has already created a post onto the forum which specifically addresses this type of issue. Below is the link of that post provided for the reference purpose.

But when you write the formula you’ll see that how the context at the back-end is getting evaluated. On the visual you’re getting the results as “2.38%”. But at the back-end see how the results are getting evaulated by your formula. Below is the formula as well as the screenshot of the results provided for the reference -

Share_%_1 = 
IF(
    CALCULATE( SUM('Date'[Hours] ) ) > 120 ,
    DIVIDE(
        DISTINCTCOUNT( 'Date'[Doc] ) , [count] , 0 ) , 
    BLANK() )

Share_Percentage_1 - Results
Share_Percentage_1 - Results413×746 33.3 KB

If you see each distinctcount is getting divided by itself rather than the total. So actually to calculate the “Share_%_1” the formula should have been like this -

Share_%_1 - Harsh = 
IF(
    SUM( 'Date'[Hours] ) > 120 ,
    DIVIDE(
        DISTINCTCOUNT( 'Date'[Doc] ) , 
        CALCULATE( DISTINCTCOUNT( 'Date'[Doc] ) , 
            ALLEXCEPT( 'Date' , 'Date'[Department] ) ) , 
        0 ) , 
    BLANK() )

Now, if you see the results still they’ll be the same. Below is the screenshot of the result provided for the reference -

Share_Percentage_1 - Revised Results
Share_Percentage_1 - Revised Results551×759 15.4 KB

But now, when you write the formula to fix the totals see how the results are getting evaluated. Below is the formula to fix the totals as well as the screenshot of the final results provided for the reference -

Share_%_1 - Harsh - Total = 
SUMX(
    SUMMARIZE(
        'Date' , 
        'Date'[Department] , 
        'Date'[Doc] , 
        "@Totals" ,
        [Share_%_1 - Harsh] ) , 
    [@Totals]
)

Final Results
Final Results739×759 20.4 KB

So you’ll observe that although at the face of the visual our results are identical but at the back-end the context considered to evaluate the results is totally different.

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

sample - Harsh.pbix (27.1 KB)

2 Likes