Get total of multiple sub-totals in a visual

[JayV]

Hi! I need to create groups of services in a visual and show a total sum of these grouped values on Jan 2020
 
My data:

Service	Value	Date
1	12	Jan 2020
2	30	Jan 2020
3	21	Jan 2020
3	32	Jan 2020
1	12	Feb 2020

The groups of services i need to create:
A: 1
B: 2
C: 3
First Grouped Total: 1, 2
Second Grouped total: 1, 2, 3

What i tried:
First i created a table for the groups called "Dimension"

Then i created a bridge table with distinct services for the relationship with the imported data

When i try creating the visual, the grand total won't match correctly, it must be "42+95" (groupedtotal_1 + groupedtotal_2)

What can i do? do i need to rethink everything? thank you!

[Anonymous · Accepted solution]

Hi @JayV 

Thanks for the reply from @danextian .

@JayV , please try the following measures:

total1 = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Service] = 1 || 'Table'[Service] = 2))

total2 = CALCULATE(SUM('Table'[Value]), FILTER('Table', 'Table'[Service] = 1 || 'Table'[Service] = 2 || 'Table'[Service] = 3))

Measure = 
VAR _total1 = CALCULATE(SUM('Table'[Value]), FILTER('Table', SELECTEDVALUE('Table (2)'[Group]) = "groupedtotal_1")) + 0
VAR _total2 = CALCULATE(SUM('Table'[Value]), FILTER('Table', SELECTEDVALUE('Table (2)'[Group]) = "groupedtotal_2")) + 0
RETURN
IF(ISFILTERED('Table (2)'[Group]), SUM([Value]), ([total1] + [total2]))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @JayV 

Based on the information you provided, try this:

IF (
    NOT ( HASONEVALUE ( 'table'[Group] ) ),
    [groupedtotal_1] + [groupedtotal_2],
    [the other measure]
)

If this address your use case, please refer to this link on how you get your questions answered quickly - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/144... 

[JayV]

Hi! Grouped totals are not measures, they are values in the column "Group", and placing them in a measure will return an invalid name (Cannot find name "[groupedtotal_1]"), how will you do this?

[danextian]

Hi @JayV ,

As mentioned, https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/14...