Data Structure - which is better for sums and cumulatives?

Power BI Data Model
1

See attached and below for table data structure example: Data Structure.xlsx (10.7 KB)

I have a dataset like this:

Name Date Cost Charge Hours
Sally 1/01/2030 500 0 10
Sally 2/01/2030 500 0 10
Sally 3/01/2030 0 1000 10
Sally 4/01/2030 0 1000 10
Muhammed 1/01/2030 250 0 5
Muhammed 2/01/2030 250 0 5
Muhammed 3/01/2030 0 500 5
Muhammed 4/01/2030 0 500 5
Muhammed 5/01/2030 0 500 5
Gavic 1/01/2030 400 0 8
Gavic 2/01/2030 400 0 8
Gavic 3/01/2030 0 800 8
Gavic 4/01/2030 0 800 8
Gavic 5/01/2030 0 800 8
Gavic 6/01/2030 0 800 8

I assumed the below structure is better but now I’m not sure. Notice how Cost and Charge are pivoted into their own column and $ includes both costs:

Name Date Type $ Hours
Sally 1/01/2030 Cost 500 10
Sally 2/01/2030 Cost 500 10
Sally 3/01/2030 Charge 1000 10
Sally 4/01/2030 Charge 1000 10
Muhammed 1/01/2030 Cost 250 5
Muhammed 2/01/2030 Cost 250 5
Muhammed 3/01/2030 Charge 500 5
Muhammed 4/01/2030 Charge 500 5
Muhammed 5/01/2030 Charge 500 5
Gavic 1/01/2030 Cost 400 8
Gavic 2/01/2030 Cost 400 8
Gavic 3/01/2030 Charge 800 8
Gavic 4/01/2030 Charge 800 8
Gavic 5/01/2030 Charge 800 8
Gavic 6/01/2030 Charge 800 8

Two questions I have:

  1. Which Data Structure do you recommend and why?
  2. Whats the simplest way to do this transform? Is there a way to use unpivot instead of replace? I feel like replace is bad practice for some reason.
2

@izzleee

In this case first one is better. You need 2 measures, might sound like duplication but the logic is simple SUM ( Table[Column] )

For second scenario:

you will have to use CALCULATE ( SUM (), Type = “Cost” ) + CALCULATE ( SUM (), Type = “Charge” ) and every subsequent measure will depend on this, the moment you start iteration and nesting, understanding why the code works or doesn’t work will be a nightmare.

Both columns might have different data types so that can be a problem, you are going to increase the number of rows, not a problem with Power BI but I don’t know how huge your data is.

You won’t be able to establish any relationship between 2 columns, maybe you need to use Scatter plot, then you can’t put them on separate axis.

1 Like