Volumes variation by Category/Field

[SofiaVaz]

Hi all, 

I'm very new on Power BI and I'm dealing with DAX from scratch, so this question may be a little basic...

The data model I'm working with has a field called "voy" (voyage number), and other one called "Units" (number of units transported).

How can I calculate de number of units varition by voyage? Basically, I want to know how the transported volumes have evolved (exemple: Variation = UnitVoy02-UnitsVoy01).   

Thank you all, 

Sofia Vaz

[AlB]

Hi @SofiaVaz

Can you show a sample of your table?

Do you want to see all variations? That is

(UnitsVoy2 - UnitsVoy1), (UnitsVoy3 - UnitsVoy2), ... , UnitsVoyN - UnitsVoyN-1

How would you like to see that? In a chart?

[SofiaVaz]

Hi AIB, 

Thanks for your reply. Unfortunatly I'm not able to share the data table, but you're right, I want to see all variations. 

I had imagine to use a waterfall chart to display this info, but the measure created (if the right way is by creating a measure...) may also be used on a table, for example, something like Voy | Units | Variation.

Thank you!

Sofia Vaz

[AlB]

@SofiaVaz

 Are the voyage numbers consecutive?

You can always share a small fragment of your table, just enough to show its structure, or with dummy data if there's confidential info

[SofiaVaz]

The voyage numbers are consecutive from 1801 to 1824, 1901 to 192x... and so on ("18" from 2018, 19 from 2019, and so on...)

As you see bellow, the same voyage have different entries and my idea was to count "Unidades2" by voyage and compare this number with the previous voyage count...

Thanks you!

[v-cherch-msft]

Hi @SofiaVaz

Please check the attached sample file. You may get the previous count with below measure. And then you may get the variation.

PreviousCount =
CALCULATE (
    SUM ( Table1[Unidades_2] ),
    FILTER ( ALL ( Table1 ), Table1[sst_voyage] = MAX ( Table1[sst_voyage] ) - 1 )
)

Variation = IF(ISBLANK([PreviousCount]),BLANK(), [Count]-[PreviousCount])

Regards,

Cherie

[SofiaVaz]

Hi @v-cherch-msft, 

Your solution also works! 

I'm sorry I didn't marked it as the solution but I've tried the other one first.

Thank you for your taking time.

Sofia Vaz

[AlB]

@SofiaVaz

OK, how about this?

1. Place a line chart in your report and place Table1[sst_voyage] in the x-axis

2.  Place this measure in values. It will give the number of units per voyage:

UnitsNumber = SUM(Table1[Unidades_2])

3. Place this measure in values. It will give you the difference in units between the current and the previous voyage:

UnitsNumberDiff =
[UnitsNumber]
    - CALCULATE (
        SUM ( Table1[Unidades_2] );
        Table1[sst_voyage]
            = SELECTEDVALUE ( Table1[sst_voyage] ) - 1
    )

This should work as long as the voyage numbers are consecutive. 

[SofiaVaz]

Good Morning @AlB,

Thank you for your help.

I've tried with your expressions but it returns an error...

I can't find out what's wrong with SELECTEDVALUE... any clue?

Thank you, 

Sofia 

[AlB · Accepted solution]

@SofiaVaz

OK, that's not allowed with the simplified syntax for the filter argument. Try this instead:

UnitsNumberDiff =
[UnitsNumber]
    - CALCULATE (
        SUM ( Table1[Unidades_2] );
        FILTER (
            ALL ( Table1[sst_voyage] );
            Table1[sst_voyage]
                = SELECTEDVALUE ( Table1[sst_voyage] ) - 1
        )
    )

Code formatted with  

[SofiaVaz]

Like that it works!

I just had to added the Value() funtion before sst_voyage because it is defined as text in the data table.

Now I just need to find a way to calculate the variation when the voyages names are not consecutive. But I'll try it alone and, if needed, I'll ask your help again.

Thank you very much,

Sofia Vaz