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

Product Profit Bucketing By Color

Hi All,

 

We have a requirement in our project, i just replicated the sccenario using AdventureworksDB.

 

sample data:

 

https://jobatfresheronline-my.sharepoint.com/:x:/g/personal/admin_jobatfresheronline_onmicrosoft_com...

 

expected visual output:

https://jobatfresheronline-my.sharepoint.com/:i:/g/personal/admin_jobatfresheronline_onmicrosoft_com...

 

 SampleGraph.png

we have to calculate the number of products, which difference between sales and cost is >200000 ,

difference between sales and cost is <200000 and >100000

and <100000

 

 

Thanks..

12 REPLIES 12
ibarrau
Super User
Super User

Hi, I think there are several ways to do this.

 

In case you are looking this validation row by row, I mean checking the difference on each row, the most common is to add a new column with two if conditions asking for the range values and the return will be text categorizing this three possibilities. This column can be created in Power Query or DAX. And easty way to categorize values is creating a group, example.

Then you can add this new column as legend in your visualization and a distinct count of productkey as values.

 

In case you want to check the difference in all sales of the product then you should create three measures, one by each range and use three values and no legend. Like this:

COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Table, Table[ProductKey] ),
            "Difference"SUM ( Table[Sales] ) - SUM ( Table[Cost] )
        ),
        [Difference] > 200000
    )
)

Hope one of this helps

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


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

Happy to help!

LaDataWeb Blog

Anonymous
Not applicable

Hi,

Is the result image displaying the correct numbers or it is just an example?

Here's what i got:

result.PNG

Thanks.

Anonymous
Not applicable

Hi @Anonymous 

 

Thanks for your reploy, the image content is just an example. Kindly share the DAX for the solution.

Anonymous
Not applicable

Hi @Anonymous ,

Here's the link to the report:

https://1drv.ms/u/s!AikPceQOhqFEhAwHyrxppGS-gvn2?e=c5BFn5

Thanks.

Anonymous
Not applicable

thanks @Anonymous 

Anonymous
Not applicable

@Anonymous  do you have a better solution than this?

Anonymous
Not applicable

Hi@Anonymous ,

No, Is it giving correct result for your data?

Thanks.

Anonymous
Not applicable

@Anonymous  we dont want any new table to be created

Hi @Anonymous ,

If you change the output of vaibhavdesai from Stacked bar chart to Stacked column chart, that will be most close to your expected output.

Capture.PNG

If you still need help, please describe your requirement in details so that we could give further advice.

Best Regards,

Cherry

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

@v-piga-msft We need to write a DAX expression without creating a new table, please help us here.

Anonymous
Not applicable

Hi @Anonymous ,

I am not sure how this can be done without a supporting table.

Thanks.

Anonymous
Not applicable

plz share dax?

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.