cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PwrBI01
Post Patron
Post Patron

Get the total variation for applying a measure on it

Hi guys,

 

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

 

Imagen3.png

 

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.
1 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"
    )

 

vjaneygmsft_0-1625134984570.png

 

vjaneygmsft_1-1625134995692.png

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

View solution in original post

9 REPLIES 9
PwrBI01
Post Patron
Post Patron

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:

Imagen1.png

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:

 

Imagen2.png

 

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.

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"
    )

 

vjaneygmsft_0-1625134984570.png

 

vjaneygmsft_1-1625134995692.png

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

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.

@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

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 NameNumber of codes/references of our delegationdel
A3.29346.142
B36.80946.142
C38546.142
D5.63946.142
E146.142
F1546.142
TOTAL46.14246.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.

@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

@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.

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

v-janeyg-msft
Community Support
Community Support

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

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors