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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
hnguyen76
Resolver II
Resolver II

Sum of values within multiple columns based on disconnected slicer table

Hi All.

 

I have a disconnected table that I'm using as a slicer and then I have this fact table that's wide rather than long. Sadly, it is not possible for me to unpivot the data as I have no control over any of the data modeling aspect. However, what I need to do is to be able to sum up the amounts based on my slicer values.

 

My disconnected slicer table has these values:

IDValue
1ABC
2DEF
3XYZ

 

While my fact table has a sample data of:

DateABCDEFXYZ
1/1/2020100200400
2/1/2020200400800
3/1/20203006001200

 

So within my slicer, if I select for example, ABC and XYZ the return that I'm expecting would be this (do note that this should still be disconnected):
expected_result.png

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION

@amitchandak  Nice! I had to make a small adjustment but the idea is a good start!

 

Measure = 
    IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 1), [ID], 1), SUM('Fact'[ABC]), BLANK()) 
+   IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 2), [ID], 2), SUM('Fact'[DEF]), BLANK()) 
+   IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 3), [ID], 3), SUM('Fact'[XYZ]), BLANK())

 

Just taking your idea, I built this a bit further to get the same results but still not as .. dynamic as I would like it. Think it is ever possible to make it any more future-proof?

A_Bit_More_Streamlined = 
VAR _Values = VALUES(Slicer[Value])
VAR _Tbl = 
GENERATE(
    _Values,
    ROW(
        "SUM",
        SWITCH( [Value],
            "ABC", SUM('Fact'[ABC]),
            "DEF", SUM('Fact'[DEF]),
            "XYZ", SUM('Fact'[XYZ])
        )))

RETURN

SUMX(_Tbl, [SUM])



View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@hnguyen76 , Try a measure like

 


if(maxx(filter(allselected(Table1), Table[Value]="ABC"),Table1[Value]), Sum(Table2[ABC]),blank())
+if(maxx(filter(allselected(Table1), Table[Value]="DEF"),Table1[Value]), Sum(Table2[DEF]),blank())
+if(maxx(filter(allselected(Table1), Table[Value]="XYZ"),Table1[Value]), Sum(Table2[XYZ]),blank())

 

 

unpivot will be the best option, If possible

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak  Nice! I had to make a small adjustment but the idea is a good start!

 

Measure = 
    IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 1), [ID], 1), SUM('Fact'[ABC]), BLANK()) 
+   IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 2), [ID], 2), SUM('Fact'[DEF]), BLANK()) 
+   IF(CONTAINS(FILTER(ALLSELECTED(Slicer), [ID] = 3), [ID], 3), SUM('Fact'[XYZ]), BLANK())

 

Just taking your idea, I built this a bit further to get the same results but still not as .. dynamic as I would like it. Think it is ever possible to make it any more future-proof?

A_Bit_More_Streamlined = 
VAR _Values = VALUES(Slicer[Value])
VAR _Tbl = 
GENERATE(
    _Values,
    ROW(
        "SUM",
        SWITCH( [Value],
            "ABC", SUM('Fact'[ABC]),
            "DEF", SUM('Fact'[DEF]),
            "XYZ", SUM('Fact'[XYZ])
        )))

RETURN

SUMX(_Tbl, [SUM])



Hi @hnguyen76 ,

You can unpivot even if the table is wide. You can try selecting the Date column and selecting Unpivot Other Columns. When your table has a new column need to be added, and the Power BI will automatically complete the unpivot.

image.pngimage.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.