Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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
Solved! Go to 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>
)
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. |
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>
)
Hi @tatenda24
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. |
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
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. |