wrong grand total using if statements

[Anonymous]

hello everyone ,

i need help with this particular problem in which my measure is not giving out the correct grand totals. i have tried so many ways that my novice mind could think of to fix this problem.

Just to describe the setup: all the sample data, measures and desired outcomes are  in the screenshot below.

The idea is that for each workername and for each “week ending” day:

• if the used hours column contains all blanks (only blank values as highlighted in yellow) within that “week ending” day  then the "new calc hours" column uses “suggested hours” column for values, else if the  used hours for in each “week ending” contains at least one value, then the new column takes “used hours” (regardless of if there can be some blank values within that week 
• 
  the problem here is that my dax  measure that I have written gets the totals correct on each row but gets the grand total wrong.

Please help me if you can, id greatly appreciate it .

here is  how i initially tried to write the code for that measure

 

as you can see below the grand total in the new calc hours is off

 

 

 here is  how i initially tried to write the code for that measure

 
i also tried to somehow try using the hasonevalue approach as the output variable to no avail. at that point i was just trying anything  to try and get it to work. please help

[daxer-almighty · Accepted solution]

It's actually very easy to do.... You just have to calculate the measure row by row over all the combinations of (week_ending_on, workername), which means you have to write the measure using SUMX. Here's something to get you started (but you have to learn more about DAX, I guess):

[Measure] =
SUMX(
    summarize(
        T[week_ending_on],
        T[workername]
    ),
    // Here, since SUMX is an iterator
    // you'll know that you have a row context
    // in which only one worker and one
    // week_ending_on are visible. Just write
    // the measure with this in mind and it
    // should work... Please learn from https://dax.guide/calculate
    // how CALCULATE interacts with row contexts.
    <your_measure>
)

[daxer-almighty · Accepted solution]

It's actually very easy to do.... You just have to calculate the measure row by row over all the combinations of (week_ending_on, workername), which means you have to write the measure using SUMX. Here's something to get you started (but you have to learn more about DAX, I guess):

[Measure] =
SUMX(
    summarize(
        T[week_ending_on],
        T[workername]
    ),
    // Here, since SUMX is an iterator
    // you'll know that you have a row context
    // in which only one worker and one
    // week_ending_on are visible. Just write
    // the measure with this in mind and it
    // should work... Please learn from https://dax.guide/calculate
    // how CALCULATE interacts with row contexts.
    <your_measure>
)

[AlB]

Hi @Anonymous 

Can you share the pbix? It'll help find a solution faster.

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Hi @AlB  I have attached the  the link for the pbix file below. it has the exact same data. Thank you for your willingness to help ! I appreciate it

https://drive.google.com/file/d/1XpVRDpw7GLNxxT9nxVwN78zhfU5eUc4a/view?

usp=sharinghttps://drive.google.com/file/d/1XpVRDpw7GLNxxT9nxVwN78zhfU5eUc4a/view?usp=sharing 

[AlB · Accepted solution]

@Anonymous 

Create a new variable based on the one you already have. See it at work in the attached file.

new applied hours TOTAL = 
SUMX (
    SUMMARIZE ( 'table', 'table'[week_ending_on], 'table'[workername] ),
    [new applied hours]
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.