cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xierwee Frequent Visitor
Frequent Visitor

create measure to pivot data

 

Hi, I am a newbee in power bi.  I have the following data in table 1. I would like to create a measure to calculate the sum of  two names selected by slicers. for each date, and then plot the result in a line chart. For instance, when I select A and B through slicer. 

 

the result should be table 2. I know in the matrix, one can some how get the table, but I couldn't find a way to plot it. Please help me, ideal case is not to use matrix. Thanks a lot in advance.

 

table 1

 

datenamevalue
20180101A0.1
20180101B0.1
20180101C0.2
20180101D0.4
20180102A0.1
20180102B0.2
20180102C0.1
20180102D0.5
20180103A0.1
20180103B0.3
20180103C0.4
20180103D0.1

 

 

table 2

datetotal
201801010.2
201801020.3
201801030.4

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: create measure to pivot data

Hi @xierwee 

 

You may try below measure:

Measure =
DIVIDE (
    SUM ( Table1[value] ),
    CALCULATE ( SUM ( Table1[value] ), ALLSELECTED ( Table1[name] ) )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
HotChilli Established Member
Established Member

Re: create measure to pivot data

I think you could probably do this without writing any DAX because the default behaviour of powerbi would be to sum the value field in any visualisation.

But if you want more control -     Measure = SUM(Table1[value])

 

Create the slicer and change the options to allow multi-selection.

Make sure the Date field has the correct Date type

Highlighted
xierwee Frequent Visitor
Frequent Visitor

Re: create measure to pivot data

you are totally correct! Woman Happy thank you!

 

Another question, if I want to divide the sum? like in table3, what should the measure look like, I tried the divide. 

 

datenamestotal
20180101A50.00%
20180101B50.00%
20180102A33%
20180102B67%
20180103A25%
20180103B75%
Community Support Team
Community Support Team

Re: create measure to pivot data

Hi @xierwee 

 

You may try below measure:

Measure =
DIVIDE (
    SUM ( Table1[value] ),
    CALCULATE ( SUM ( Table1[value] ), ALLSELECTED ( Table1[name] ) )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
xierwee Frequent Visitor
Frequent Visitor

Re: create measure to pivot data

cool, thank you very much!