Forum Discussion

Helper III

4 years ago

Solved

Visual to show data change in time

Hello, I would like to know if there is a visual I can use to measure the variation of a certain quantity month by month possibly splitted by category.

Example

I have a table

Subscription date

Country

I would like to see, in the last six months some thing like

	December 2021	November 2021	October 2021	September 2021	Agoust 2021	June 2021
US	381 (-11%)	428 (+7%)	400 (+12%)	...	...	...
FR	123 (+17%)	105 (-3%)	108 (-9%)	...	...	...
IT	762 (-6%)	810 (+15%)	705 (+24%)	...	...	...

It shouldn't be necessarily a matrix of course

Need Help

v-zhangti

4 years ago

Hi, fabiomanniti

If you don't process the data in advance on the desktop, you won't get your desired results directly through the visuals.

Maybe you can try the following methods.

Column:

last date = DATEADD('Table'[Subscription date],-1,MONTH)

Measure:

Difference =
VAR N1 =CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [Country] = MAX ( 'Table'[Country] )
                && [ID] = MAX ( 'Table'[ID] ) ) )
VAR N2 =CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (  ALL ( 'Table' ),
            [Country] = MAX ( 'Table'[Country] )
                && [Subscription date] = MAX ( 'Table'[last date] ) ) )
RETURN DIVIDE ( N1 - N2, N2 )

Best Regards,

Community Support Team _Charlotte

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

Visual to show data change in time.pbix49 KB

4 Replies

AlexisOlson
Super User
4 years ago
There are some visuals that are specifically for this. For example:

3AG Systems - Bar Chart With Relative Variance

Variance Chart - xViz

Zebra BI Variance Charts

More options:

https://datadriven.pro/2019/04/16/9-ways-to-do-variance-analysis-in-power-bi/
- fabiomanniti
  Helper III
  4 years ago
  Yes, these tools per se look good but, for I understood, I must already have data worked on Desktop...
  I mean: if I only have this table
  ID Subscription date Country
  1 01/01/2019 IT
  2 02/01/2019 FR
  3 03/01/2019 US
  
  Can I know ONLY with a visual the id counting for each Country and each month (for only the last n months) and see the changing ratio compared to the previous month?
  
  If I can do it with those visual then I think I didn't understand how
  - v-zhangti
    Community Support
    4 years ago
    Hi, fabiomanniti
    
    If you don't process the data in advance on the desktop, you won't get your desired results directly through the visuals.
    
    Maybe you can try the following methods.
    
    Column:
    
    last date = DATEADD('Table'[Subscription date],-1,MONTH)
    
    Measure:
    
    Difference = VAR N1 =CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Country] = MAX ( 'Table'[Country] ) && [ID] = MAX ( 'Table'[ID] ) ) ) VAR N2 =CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Country] = MAX ( 'Table'[Country] ) && [Subscription date] = MAX ( 'Table'[last date] ) ) ) RETURN DIVIDE ( N1 - N2, N2 )
    
    Best Regards,
    
    Community Support Team _Charlotte
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Visual to show data change in time.pbix49 KB