Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Difference as total

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Difference as total

02-22-2021
08:13 AM

Hi, I have a table with data from two different reports., each type has the same attributes.

Is there a way to make the BI display the difference in the total? I mean to avoid creating one measure for each attribute for each type and calculate the difference.

Thank you very much.

TYPE | QTY | AMOUNT | PROFIT |

A | 26 | 2600 | 165 |

B | 32 | 1252 | 100 |

Diff | -6 | 1348 | 65 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-24-2021
05:48 PM

Hi @marcos_osorio ,

In power bi, Dax is calculated based on context and columns, if you want to get the same result in the table, you need to create three simliar measures:

```
QTY_ =
IF (
HASONEVALUE ( 'Table A'[QTY] ),
SUM ( 'Table A'[QTY] ),
CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "B" )
)
Amount_ =
IF (
HASONEVALUE ( 'Table A'[AMOUNT] ),
SUM ( 'Table A'[AMOUNT] ),
CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "B" )
)
PROFIT_ =
IF (
HASONEVALUE ( 'Table A'[PROFIT] ),
SUM ( 'Table A'[PROFIT] ),
CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "B" )
)
```

If you only want to create one measure, you need to unpivot your table and use a matrix to show that:

```
Diff =
CALCULATE (
SUM ( 'Table A - Unpivot'[Value] ),
'Table A - Unpivot'[TYPE] = "A"
)
- CALCULATE (
SUM ( 'Table A - Unpivot'[Value] ),
'Table A - Unpivot'[TYPE] = "B"
)
```

Attached a sample file in the below, hopes it could help.

Best Regards,

Community Support Team _ Yingjie Li

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

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-24-2021
05:48 PM

Hi @marcos_osorio ,

In power bi, Dax is calculated based on context and columns, if you want to get the same result in the table, you need to create three simliar measures:

```
QTY_ =
IF (
HASONEVALUE ( 'Table A'[QTY] ),
SUM ( 'Table A'[QTY] ),
CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[QTY] ), 'Table A'[TYPE] = "B" )
)
Amount_ =
IF (
HASONEVALUE ( 'Table A'[AMOUNT] ),
SUM ( 'Table A'[AMOUNT] ),
CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[AMOUNT] ), 'Table A'[TYPE] = "B" )
)
PROFIT_ =
IF (
HASONEVALUE ( 'Table A'[PROFIT] ),
SUM ( 'Table A'[PROFIT] ),
CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "A" )
- CALCULATE ( SUM ( 'Table A'[PROFIT] ), 'Table A'[TYPE] = "B" )
)
```

If you only want to create one measure, you need to unpivot your table and use a matrix to show that:

```
Diff =
CALCULATE (
SUM ( 'Table A - Unpivot'[Value] ),
'Table A - Unpivot'[TYPE] = "A"
)
- CALCULATE (
SUM ( 'Table A - Unpivot'[Value] ),
'Table A - Unpivot'[TYPE] = "B"
)
```

Attached a sample file in the below, hopes it could help.

Best Regards,

Community Support Team _ Yingjie Li

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-22-2021
08:22 AM

@marcos_osorio yes if you unpivot your data then you don't need to write measure for each column, only 3 measures will be required in that case.

**Check my latest blog post **Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions** **I would ❤ **Kudos** if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡**Visit us at ****https://perytus.com****, your one-stop-shop for Power BI-related projects/training/consultancy.****⚡**

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

Featured Topics

Top Kudoed Authors

User | Count |
---|---|

364 | |

223 | |

98 | |

69 | |

64 |