Get the total variation for applying a measure on it

[PwrBI01]

Hi guys,

I want to get the variation of the last year to apply a measure on it, how could I get it?

I have 3 tables:

Table 1. It is called Name and it contains the name of the sales agents.

Table 2. It is called Sales and it contains the sales transactions and the number of the sales agents.

Table 3. It is called Calendar table and it contains the calendar.

I have applied the measure:

CALCULATE(TOTALYTD(COUNTX(ALL(NAME);NAME[NAME]);CALENDAR TABLE [DATE]);NAME[MAIN OPERATION]="YES")

but it doesn't work.

Thanks in advance.

Regards.

[PwrBI01]

Hi @v-janeyg-msft,

Thank you for your answer,

You are right, my explanation was really bad and it was really hard to understand it, so I will try to explain it in another way and bringing a data reference.

I have the following tables:

And I want to create a conditional formatting that if the value is higher than the one with the blue circle it should be in green, if it is lower it should be in red and if it is similar it should be in yellow, as you can see in the following photo:

I have created that for the first table but I also need the same for the second table, but it is similar.

I need to create a measure for obtain the value of the blue circles (one measure for each value) so then I can apply the following measure:

color measure =

var Total = -----------------------------------------

Return

switch(true();

[Variation %] > Total;"green";

[Variation %] = Total;"yellow";

[Variation %] < Total;"red")

What I need to create is the measure of the red part of the measure.

The file is in the following link: https://www.dropbox.com/s/604zd1onn39ox18/Problem%20conditional%20formatting%204.pbix?dl=0

Thanks in advance.

Regards.

[v-janeyg-msft · Accepted solution]

Hi, @PwrBI01 

The idea is that you need to calculate a single result separately.

Like this:

Measure =
VAR total =
    SUMX (
        SUMMARIZE (
            ALL ( 'Name of vendors' ),
            "a",
                VAR CurrentPeriod = [Number of documents]
                VAR PreviousPeriod =
                    CALCULATE (
                        [Number of documents],
                        DATEADD ( 'Calendar table'[Date], -12, MONTH )
                    )
                RETURN
                    DIVIDE ( CurrentPeriod - PreviousPeriod, PreviousPeriod )
        ),
        [a]
    )
RETURN
    SWITCH (
        TRUE (),
        [Variation %] > total, "green",
        [Variation %] = total, "yellow",
        "red"
    )

The same is true for the other table, you need to calculate the value of F.

Best Regards

Janey Guo

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

[PwrBI01]

Thank you very much, it works correctly for the first table, but I don't know how to addapt it to use it for the second one.

Regards.

[v-janeyg-msft]

@PwrBI01  I have written it in the sample file, Please check it.

Best Regards

Janey Guo

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

[PwrBI01]

Hi @v-janeyg-msft, 

I am really sorry, but I am trying to addapt it to a new table and I have a question for calculate 'del' inside 'Measure 2'.

Imagine we can sum A + B + C + D + E + F and we get the following total.

Column Name	Number of codes/references of our delegation	del
A	3.293	46.142
B	36.809	46.142
C	385	46.142
D	5.639	46.142
E	1	46.142
F	15	46.142
TOTAL	46.142	46.142

I want to get the right column called 'del' but when I take your measure:

VAR del =
    CALCULATE (
        SUM ( 'Reference table'[Number of references] );
        FILTER (
            ALL ( 'Column for anual statistics' );
            'Column for anual statistics'[Column name] = "F"
        );
        FILTER ( 'Reference table'; 'Reference table'[Type number] = 99 );
        USERELATIONSHIP ( 'Calendar table'[Date]; 'Reference table'[Date] )
    )

And change it into the following one:

VAR del =
    CALCULATE (
        SUM ( 'Reference table'[Number of references] );
        ALL ( 'Column for anual statistics' );
        FILTER ( 'Reference table'; 'Reference table'[Type number] = 99 );
        USERELATIONSHIP ( 'Calendar table'[Date]; 'Reference table'[Date] )
    )

It doesn't work. There is a problem with all function (because it doesn't work, I can delete it and I get the same result) but I don't know how to correct it.

Thanks in advance.

Regards.

[v-janeyg-msft]

@PwrBI01   The measure I wrote is for F. The conditions of abcdef in your measure are different, so of course you won’t get total. 

Because your abcdef is different, total is not easy to calculate, you need to set the variable of abcdef in the measure, and then add them to get total.

If you can’t solve it, I suggest you open a new case and describe your problem.

Best Regards

Janey Guo

[PwrBI01]

@v-janeyg-msft I am trying to adapt it to a new table in which there is a same measure for abcdef. I just need to make that the measure takes the total and doesn't take into account a,b,c,d,e and f separately to use it, because the rest of the columns are similar to the second chart but in a same measure (they have a total for our delegation, total for all and I want to calculate our total over the global total).

Regards.

[PwrBI01]

Hi @v-janeyg-msft, thank you very much.

[v-janeyg-msft]

Hi, @PwrBI01 

I really want to help you but your description doesn’t seem to be very clear, and there is no data reference.

Can you explain more details and share some sample data and your desired result like? So we can help you soon.

Best Regards

Janey guo