Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
We have a requirement in our project, i just replicated the sccenario using AdventureworksDB.
sample data:
expected visual output:
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..
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.
Happy to help!
Hi,
Is the result image displaying the correct numbers or it is just an example?
Here's what i got:
Thanks.
Hi @Anonymous
Thanks for your reploy, the image content is just an example. Kindly share the DAX for the solution.
Hi @Anonymous ,
Here's the link to the report:
https://1drv.ms/u/s!AikPceQOhqFEhAwHyrxppGS-gvn2?e=c5BFn5
Thanks.
thanks @Anonymous
@Anonymous do you have a better solution than this?
Hi@Anonymous ,
No, Is it giving correct result for your data?
Thanks.
@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.
If you still need help, please describe your requirement in details so that we could give further advice.
Best Regards,
Cherry
@v-piga-msft We need to write a DAX expression without creating a new table, please help us here.
Hi @Anonymous ,
I am not sure how this can be done without a supporting table.
Thanks.
plz share dax?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |