Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

wrong grand total using if statements

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's avatar
    daxer-almighty
    5 years ago

    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's avatar
    AlB
    5 years ago

    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.

     

4 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.