Sumproduct in Power BI

[yogeshmaney]

Hello

Have Data as

Unit      |  Category 

5           |  Cat1,Cat2

3           |  Cat2

Want Result as

Cat1  = 5

Cat2  = 8

[Phil_Seamark]

Hi @yogeshmaney

I reckon you use the Split Column function in the Query Editor to split your Category column.  Then unpivot the tables.

So 

1. In the Query Editor, select the Category Column
2. Select Split Column from the Transform Tab and split on Comma
3. Highligt the Unit column and then click Unpivot Columns -> Unpivot other columns
4. Remove the Attribute column and return the data back to Power BI

Back in Power BI you can then just drag the two columns to a grid and you should see your result

I'd recommend making a SUM measure as you develop your model but this should give you the idea,

[yogeshmaney]

Thanks For the reply!

But my Unit part is in Duration and converted as Measure and Column. Also the unpivot removes the other related data.

On Slicer Selection , it should show the Category graph

In Excel would have calculated with SUMPRODUCT(--($B$2:$C$3=F4)*$D$2:$D$3)

Thanks,

---Yogi

[Phil_Seamark]

Hi @yogeshmaney

I'm not sure what you mean by "converted as Measure and Column".  

The other option with Unpivot is to select the columns created by the Split Column and just select and unpivot those.

[yogeshmaney]

Hi,

The Unit part is in Duration (00:00:05 format), So I Converted it to Sec with New Measure and New Column.

 Display Category Slipt  On Select Slicer conditions

Regards,

Yogi 

[ElliotP]

Was the unit part supposed to be a duration or simply a whole number/decimal value.

I agree with @Phil_Seamark, splitting the columns and then summing is the best way.

[Phil_Seamark]

@ElliotP, I think there is other important data that we are not seeing in the sample supplied so difficult to understand the requirement without seeing the bigger picture.

[ElliotP]

@Phil_SeamarkAgreed.

[yogeshmaney]

Here's the Data Sample ,

The Brand Categories >Spitted with "," delimiter.

Duration of Avail > Converted to Sec with New Column

TCR> Trimmed to Minute Part

After Brand Categories split , Its spitted to two Col Category.1 and Category.2

When charted , considers only Category.1 , So giving Food value as 5 and Drinks as 3. Should give Food 5 and Drinks 8

Thanks, 

[v-yuezhe-msft]

Hi @yogeshmaney,

How about creating a new table using the following formula?

tablenew = SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
            Table2,
            [Duration of Avail],
            [Brand categories],
"SubCnt",
            1 + Len ( [Brand categories] ) - Len ( Substitute(Table2[Brand categories], ",", "" ) )
          ),
          DummyTbl
        ),
        DummyTbl[Dummy] <= [SubCnt]
      ),
"Brand",
pathitem (
Substitute ( Table2[Brand categories], ",", "|" ),
        DummyTbl[Dummy]
      )
    ),
    [Duration of Avail],
    [Brand]
  )

Reference:
http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

Thanks,
Lydia Zhang

[yogeshmaney]

Great Thanks !

is there way to avoid blank row? Can link relationship between Calculated  Table and main data

[v-yuezhe-msft]

Hi @yogeshmaney,

Change DAX to:

tablenew = FILTER(SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
            Table2,
            [Duration of Avail],
            [Brand categories],
"SubCnt",
            1 + Len ( [Brand categories] ) - Len ( Substitute(Table2[Brand categories], ",", "" ) )
          ),
          DummyTbl
        ),
        DummyTbl[Dummy] <= [SubCnt]
      ),
"Brand",
pathitem (
Substitute ( Table2[Brand categories], ",", "|" ),
        DummyTbl[Dummy]
      )
    ),
   [Duration of Avail],
    [Brand]
  ),
	NOT(ISBLANK(Table2[Duration of Avail])))

What field you want to use to create relationship? The field of one table must have unique values when creating relationship.

Thanks,
Lydia Zhang

[yogeshmaney]

Hello, Tweaked little, Can't establish relation  tablenew[Sr] and Table2[Sr.] ?

tablenew = FILTER(SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Table2, [Sr.],
[DurationofAvail],
[BrandCategories],
"SubCnt",
1 + Len ( [Brandcategories] ) - Len ( Substitute(Table2[Brandcategories], ",", "" ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
"Brand",
pathitem (
Substitute ( Table2[Brandcategories], ",", "|" ),
DummyTbl[Dummy]
)
),[Sr.],
[DurationofAvail],
[Brand]
),
NOT(ISBLANK(Table2[Sr.])))

[v-yuezhe-msft]

Hi @yogeshmaney,

You can use Brand categories field in the two tables to make relationship.

Thanks,
Lydia Zhang

[yogeshmaney]

Hi,

Please check the April Update . Option of Delimiter Split into rows. 

Regards,