Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Radial Gauge based on multiple datasets

Hi,

 

I have the following two tables linked to a number of different visuals. Both data sets are dependent on slicers for Date, Product and Retailer.

 

Sales

 

Date         Client  Product   Qty    Sales

Mar-18AApples2 $       2.00
Mar-18BPears3 $       6.00
Mar-18CApples4 $       4.00
Mar-18APears3 $       6.00
Mar-19BApples2 $       2.00
Mar-19BPears2 $       4.00
Mar-19AApples2 $       2.00
Mar-19CPears6 $    12.00
Mar-19AApples4 $       4.00

 

Forecasts

Date        Product    Retailer  Forecast

Mar-19ApplesA $    15.00
Mar-19ApplesB $    20.00
Mar-19ApplesC $    15.00
Mar-19PearsA $    30.00
Mar-19PearsB $    20.00
Mar-19PearsC $      5.00

 

I would like to create a radial gauge based on the following values:

 

Maximum value - based on the total forecasts for the month. If I choose a product/retailer, the value must update accordingly. IE If I choose Apples - $50, if I choose Retailer A - $45, if I choose Retailer B & Pears - $20.

 

Target value - based on the total amount sold last year in the same month IE. What was sold in March 2018. Again, this needs to update accordingly with the slicer for product/retailer/date. 

 

 

I hope this explains it clearly. Thanks in advance

Peter

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create slicer tables and link them as below.Then create a measure.Attached sample file for your reference.

    Last Year Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ALLSELECTED ( Sales ),
        YEAR ( Sales[Date] )
            = YEAR ( MAX ( Forecasts[Date] ) ) - 1
            && MONTH ( Sales[Date] ) = MONTH ( MAX ( Forecasts[Date] ) )
    )
)

    Regards,

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous 

    You may create slicer tables and link them as below.Then create a measure.Attached sample file for your reference.

    Last Year Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ALLSELECTED ( Sales ),
        YEAR ( Sales[Date] )
            = YEAR ( MAX ( Forecasts[Date] ) ) - 1
            && MONTH ( Sales[Date] ) = MONTH ( MAX ( Forecasts[Date] ) )
    )
)

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks.

       

      1. Maximum value - I forgot to mention that it needs to be date relative as well. I've tried creating a relationship between the dates on my forecast table and the dates on my sales table by it said it can't because its a many-to-many relationship. So I tried creating a distinct date table but it broke my main sales table.

       

      2. Target value - I keep getting a syntax error. It says:

      The syntax for ')' is incorrect. (DAX(CALCULATE ( SUM ('Sell In'[Sales]), FILTER ( ALLSELECTED ( 'Sell In'), YEAR ( 'Sell In'[Month.] In ) = YEAR ( MAX ( 'Sell Out'[Date].[Date] ) ) - 1 && MONTH ( 'Sell In'[Month.]) = MONTH ( MAX ( 'Sell Out'[Date].[Date] ) ) )))).

       

      Thanks

       

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous 

        You may format your formula here and it could point out the incorrect syntax ')' .If you need further help,please share your .pbix file or more sample data which could reproduce your scenario and your desired output.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

        Regards,