Forum Discussion
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
| date | name | value |
| 20180101 | A | 0.1 |
| 20180101 | B | 0.1 |
| 20180101 | C | 0.2 |
| 20180101 | D | 0.4 |
| 20180102 | A | 0.1 |
| 20180102 | B | 0.2 |
| 20180102 | C | 0.1 |
| 20180102 | D | 0.5 |
| 20180103 | A | 0.1 |
| 20180103 | B | 0.3 |
| 20180103 | C | 0.4 |
| 20180103 | D | 0.1 |
table 2
| date | total |
| 20180101 | 0.2 |
| 20180102 | 0.3 |
| 20180103 | 0.4 |
Hi xierwee
You may try below measure:
Measure = DIVIDE ( SUM ( Table1[value] ), CALCULATE ( SUM ( Table1[value] ), ALLSELECTED ( Table1[name] ) ) )Regards,
Cherie
4 Replies
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
you are totally correct! :womanhappy: thank you!
Another question, if I want to divide the sum? like in table3, what should the measure look like, I tried the divide.
date names total 20180101 A 50.00% 20180101 B 50.00% 20180102 A 33% 20180102 B 67% 20180103 A 25% 20180103 B 75% Hi xierwee
You may try below measure:
Measure = DIVIDE ( SUM ( Table1[value] ), CALCULATE ( SUM ( Table1[value] ), ALLSELECTED ( Table1[name] ) ) )Regards,
Cherie
