Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @Anonymous 

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

1.png

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,

Community Support Team _ Cherie Chen
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

3 REPLIES 3
v-cherch-msft
Employee
Employee

Hi @Anonymous 

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

1.png

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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



 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.