Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, I have the following problem:
I have hierarchy of dates (year - quarter - month), and a set of values associated with these dates. There's also a need to add additional rightmost column to matrix visual that should contain a difference between values. To do that, I had to add one more row into my main table where value in month column equals to v_difference. The table looks like this:
The following measure code displays 1 for v_difference, and 101 otherwise:
sum_value =
VAR curr_year =
SELECTEDVALUE ( 'Table'[year] )
VAR curr_quarter =
SELECTEDVALUE ( 'Table'[quarter] )
VAR curr_month =
SELECTEDVALUE ( 'Table'[month] )
RETURN
IF (
NOT ( ISBLANK ( curr_year ) ),
IF (
NOT ( ISBLANK ( curr_quarter ) ),
IF ( curr_month <> "v_difference", COUNT ( 'Table'[value] ) + 100, 1 )
)
)
Here's the visual:
Now, the question: I noticed that Total column on the level of quarter is blank. I would like it to be equal to the sum of q1, q2, q3 and q4 (i.e. 404). Is there a way to do that?
Here's the link to Google Drive with pbix file: https://drive.google.com/file/d/1k-HP2wQZ52K8_nia1eMQWK4b2MWQdM8a/view?usp=sharing
Thank you very much.
Solved! Go to Solution.
Hi , @a_gaisin
Try the measure as below:
sum_value =
var tab=
SUMMARIZE(
'Table',
'Table'[year],
'Table'[quarter],
'Table'[month],
"Re",
IF(
NOT(ISBLANK([year])),
IF(
NOT(ISBLANK([quarter])),
IF(
[month]<>"v_difference",COUNT('Table'[value])+100,1
)
)
)
)
return
SUMX(
tab,
[Re]
)
The result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi , @a_gaisin
Try the measure as below:
sum_value =
var tab=
SUMMARIZE(
'Table',
'Table'[year],
'Table'[quarter],
'Table'[month],
"Re",
IF(
NOT(ISBLANK([year])),
IF(
NOT(ISBLANK([quarter])),
IF(
[month]<>"v_difference",COUNT('Table'[value])+100,1
)
)
)
)
return
SUMX(
tab,
[Re]
)
The result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@a_gaisin , try like
New sum_value = sumx(summarize(Table,table[year],table[qtr],"_1",[sum_value]),[_1])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |