Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a_gaisin
Frequent Visitor

total is blank for higher level in hierarchy

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:

table.PNG

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:

visual.PNG
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.

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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:

46.png

 

 

 

 

 

 

 

 

 

sample pbix attached

 

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.

 

View solution in original post

2 REPLIES 2
v-easonf-msft
Community Support
Community Support

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:

46.png

 

 

 

 

 

 

 

 

 

sample pbix attached

 

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.

 

amitchandak
Super User
Super User

@a_gaisin , try like

New sum_value = sumx(summarize(Table,table[year],table[qtr],"_1",[sum_value]),[_1])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.