The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I am trying to create a matrix and I was wondering if it is possible to use a text column as a value in a matrix.
My table looks like this:
*this shows services offered to each customer, together with the invoice amount and invoice unit
**GB = gigabyte, HRS = hours
And I would like to create a matrix that looks like this:
When I do this in Power BI, it only shows the 'First Invoice Unit' (GB in this case). And GB is of course not applicable for services by the hour.
I guess this means tricking Power BI into not doing any sums on the 'Invoice Unit' field, but simply look at what is the only possible invoice unit when the service is backup (that would be GB), and what is the only possible invoice unit when the service is hours (that would be HRS). But I'm not sure if that's even possible.
Thanks in advance!
Best regards,
Ruxandra
Solved! Go to Solution.
Hi @Anonymous ,
If the detail of the Invoice unit has only one value per each Service then when you select the first or the last value in the matrix should return the expected value, however if for Hours you have more than one Invoice unit then you will get incorrect values.
How does your data model looks like?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!
Hi @Anonymous ,
If the detail of the Invoice unit has only one value per each Service then when you select the first or the last value in the matrix should return the expected value, however if for Hours you have more than one Invoice unit then you will get incorrect values.
How does your data model looks like?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
You were right with this. I'm not sure what happened, but Power BI only showed one invoice unit for all services (as can be seen in my screenshot), even though each service had its unique single invoice unit. At some point, it just changed to the correct distribution, but i have no idea why. No changes were done in the data source.
But anyway, thanks a lot!
Regards,
Ruxandra
Hi @MFelix
Each service (Asset_Subtype below) has only one Invoice Unit (e.g. storage space can only be sold in GB).
"None" for example has indeed more categories with different invoice units, but i filtered that out and the problem persists.
What I want to do in Power BI is this (small orange box is a customer name and the big box covers amounts):
So maybe it's not because a service has more invoice units, but a customer has more services which automatically implies more invoice units per customer?
Are you aware of any workaround?
Thanks!
Kind regards,
Ruxandra
Hi @Anonymous ,
What is the setup between the Asset_subtype and your invoice table? do you have a relationship between them?
Do you save the asset subtype and units on the invoiced table?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêshI @Anonymous ,
One workaround can be to create a concatenated value with the values and units something similar to this:
Measure =
VAR temp_table =
SUMMARIZE (
Sales;
Sales[asset_subtype];
Sales[invoiced_units];
"@Sales"; SUM ( Sales[Amount] )
)
RETURN
IF (
HASONEFILTER ( Sales[asset_subtype] );
CONCATENATEX (
temp_table;
[@Sales] & " " & Sales[invoiced_units];
UNICHAR ( 10 )
);
SUM ( Sales[Amount] )
)
I'm assuming that the sales tabel has asset and units.
Result will be this:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI don't see your original table.
Hello,
That should be fixed now, thanks for the notice!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |